Function Problems

Separate Letters and Numbers in the Same Excel Cell

Ayan MasoodAyan Masood Nov 12, 2026 868 views

Question details

Split a cell containing combined letters and digits into separate text and number results.

Separate Letters and Numbers in the Same Excel Cell
Product
Microsoft Excel
Device & OS
not provided
Scenario
A value such as ABC123 is stored in one cell without a separator, and the user needs to identify the first numeric character to extract each portion.
Observed behavior
Letters and numbers are combined in the same cell and need to be returned in separate columns.
Before you start

Confirm that each value begins with letters followed by numbers, such as ABC123. If digits can appear before letters or in multiple positions, use a formula designed for that specific pattern.

Solution 1Recommended

Use a Dynamic Array Formula in Excel 365

Use this method when you have Excel 365 and want one formula to spill the text and numeric portions into adjacent columns.

The formula checks every character in A1, finds the first digit, and uses that position to return the letters before it and the remaining characters after it.

1
Select the first output cell

Click an empty cell beside the mixed value, such as B1 when the original value is in A1.

2
Enter the split formula

Type or paste =LET(x,A1,p,XMATCH(TRUE,ISNUMBER(--MID(x,SEQUENCE(LEN(x)),1)),0),HSTACK(LEFT(x,p-1),MID(x,p,LEN(x)))) and press Enter.

3
Review the spilled results

Excel returns the letter portion in the formula cell and the number portion in the next column. For example, ABC123 in A1 returns ABC in B1 and 123 in C1.

4
Fill the formula down

Drag the fill handle from the formula cell down the column to process additional rows.

Use a Dynamic Array Formula in Excel 365
Two-column result created: This formula automatically spills into two columns, so keep the cell to the right empty.

Split Mixed Letters and Numbers with WPS Spreadsheets

WPS Spreadsheets can separate text and number portions by finding the first digit with a formula, then using LEFT and MID to extract each result into its own column.

  1. 1. Open the worksheet in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the worksheet containing the mixed values in column A.
  2. 2. Find the first numeric character: Click B1 and enter =MIN(IFERROR(FIND({"0","1","2","3","4","5","6","7","8","9"},A1),"")) to calculate the first digit position.
  3. 3. Return the letter portion: Click C1 and enter =LEFT(A1,B1-1) to extract the characters before the first digit.
  4. 4. Return the numeric portion: Click D1 and enter =MID(A1,B1,LEN(A1)) to extract the remaining characters from the first digit onward.
  5. 5. Apply the formulas to all records: Select B1:D1 and drag the fill handle downward to separate every value in the list.
Use FIND, LEFT, MID, and IFERROR formulas to split values such as ABC123.Process many rows quickly by filling formulas down a worksheet.Open and edit Microsoft Excel .xlsx and .xls files with compatible spreadsheet formatting.Use a lightweight, familiar spreadsheet interface for formula-based data cleanup.
microsoft office alternative - wps office

Frequently Asked Questions

How do I find the first number in an Excel cell containing text?

Use =MIN(IFERROR(FIND({"0","1","2","3","4","5","6","7","8","9"},A1),"")). It searches for each digit in A1 and returns the earliest position found.

How can I extract letters before the first number in Excel?

If B1 contains the position of the first digit, use =LEFT(A1,B1-1). For ABC123, the formula returns ABC.

How do I extract the number portion after letters in a cell?

If B1 contains the first digit position, use =MID(A1,B1,LEN(A1)). For ABC123, the formula returns 123.

Will the formula work when the numeric portion contains decimal points or hyphens?

Yes, as long as the first digit marks the beginning of the numeric portion. The MID formula returns every character from that first digit onward, including decimal points or hyphens.

Why does the Excel 365 formula need empty cells on the right?

The HSTACK function returns two results side by side. Excel needs the adjacent output cell to be empty so the formula can spill the number portion into that column.