Function Problems

Split Mixed Text Before and After the First Letter in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 12, 2026 868 views

Question details

Split each mixed Excel value into the leading numeric portion and the remaining letter-and-number portion.

Split Mixed Text Before and After the First Letter in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula beside the first code

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))))

2
Press Enter and review the spilled results

Press Enter. Excel returns the numeric prefix in B2 and the letter-and-number suffix in C2; for example, 2545B1500 becomes 2545 and B1500.

3
Apply the formula to additional rows

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.

Use a Microsoft 365 formula to split at the first nonnumeric character
Expected result: For 143B0, the formula returns 143 in the first result column and B0 in the second result column.
WPS Spreadsheet solution

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. 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. 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. 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. 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.
Separates values such as 143B0 into 143 and B0 automaticallyLets you fill one formula down across large code listsSupports common Excel worksheet formulas and .xlsx file compatibilityProvides a familiar spreadsheet interface for working with Excel-style data
microsoft office alternative - wps office

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.