Split Mixed Text Before and After the First Letter in Excel
Question details
Split each mixed Excel value into the leading numeric portion and the remaining letter-and-number portion.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A worksheet column contains codes such as 143B0, 2545B1500, and 1822P39, and the user needs separate values for the numeric prefix and suffix.
- Observed behavior
- The source codes combine an initial sequence of digits with a suffix that begins at the first letter.
Make sure every source code begins with one or more digits and that the first nonnumeric character marks the start of the suffix. Place the formula in empty adjacent cells so the two results can spill into separate columns.
Use a Microsoft 365 formula to split at the first nonnumeric character
Use this method when you have Microsoft 365 and want the numeric prefix and remaining suffix returned automatically in two columns.
The formula finds the first character that is not a number, then returns everything before it and everything from that character onward.
If the mixed value is in cell A2, select an empty cell such as B2 and enter: =LET(x,A2,p,XMATCH(FALSE,ISNUMBER(--MID(x,SEQUENCE(LEN(x)),1)),0),HSTACK(LEFT(x,p-1),MID(x,p,LEN(x))))
Press Enter. Excel returns the numeric prefix in B2 and the letter-and-number suffix in C2; for example, 2545B1500 becomes 2545 and B1500.
Copy the formula down from B2, or convert the source range to a table with Insert > Table so Excel fills the calculation for new rows.

Split a large data set with Power Query
Use Power Query when the source contains many rows and you want a repeatable transformation that can be refreshed after data changes.
Split Codes at the First Letter with WPS Spreadsheet
WPS Spreadsheet can separate codes containing a numeric prefix and a letter-number suffix with a formula placed beside the source data. Use the same first-nonnumeric-character logic, then fill the formula down to process the entire column.
- 1. Open the worksheet in WPS Spreadsheet: Open the Excel workbook in WPS Office and select an empty cell next to the first source code, such as B2 when the code is in A2.
- 2. Enter the split formula: Enter =LET(x,A2,p,XMATCH(FALSE,ISNUMBER(--MID(x,SEQUENCE(LEN(x)),1)),0),HSTACK(LEFT(x,p-1),MID(x,p,LEN(x)))) in B2.
- 3. Confirm the two returned values: Press Enter to return the digits before the first letter in B2 and the remaining suffix in the adjacent cell.
- 4. Fill the calculation through the column: Select the formula cell and drag the fill handle down, or copy and paste it into the remaining rows that contain codes.

Frequently Asked Questions
How does the formula know where to split each code?
It checks each character from left to right and identifies the first one that cannot be converted to a number. The split occurs immediately before that character.
Will this split 1822P39 correctly?
Yes. With 1822P39 in A2, the formula returns 1822 as the numeric prefix and P39 as the suffix.
Can I use this formula if the suffix starts with a symbol instead of a letter?
Yes. The formula splits at the first nonnumeric character, so a code such as 143-20 is separated into 143 and -20.
Why do I see a spill error after entering the formula?
The formula returns two results side by side. Clear the cell immediately to the right of the formula cell, and make sure the formula is not entered inside an Excel table that blocks spilled arrays.




