Sometimes in Excel you’ll get data where text and numbers are combined, either intentionally or by mistake. For example, you might have a product code with letters followed by numbers (FX5200), or a number with text after it (2021y). You might need to separate the numbers from the text — or split letters from numbers in alphanumeric codes.
By default, Excel does not have a built-in function to do this directly. But yes, it’s possible — and below is a step-by-step guide.
Before starting, it’s a good idea to first find cells containing numbers and also check that those cells contain letters. This way, you can save processing time if your dataset is large.
In this article, you’ll see different ways to separate text and numbers — using Excel’s built-in functions, Google Sheets, and the !SEMTools add-in.
Note: This page covers separating text and numbers within the same cell. If you only need to extract numbers into another cell, see: extract numbers from a cell in Excel.
Separate numbers from text using Excel formulas
These formulas work best when the arrangement of text and numbers in your cells follows a predictable pattern. Common cases include:
- letters before numbers (x100, FX5200, MI6);
- numbers before letters (2021y, 3D, 4K, 4.2Ghz);
- number-text-number (3k2, 22st3, 1ABC23).
Separate numbers at the end of text
If you know the cell starts with letters and ends with numbers, you first need to find the position of the first number character. Here’s an array formula that does it:
{=MATCH(FALSE,ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),0)}Array formula note: In Excel 365 and Excel 2021, just press Enter. In Excel 2019, 2016, 2013, and earlier, you must enter it with Ctrl + Shift + Enter.
Once you have the position, you can use LEFT and MID to split the parts:
=LEFT(A1,B1-1)=MID(A1,B1,1000)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | First digit at | Text part | Number part | In one cell |
| 2 | x100 | 2=MATCH(FALSE,ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),0) | x=LEFT(A2,B2-1) | 100=MID(A2,B2,1000) | x 100=LEFT(A2,B2-1)&" "&MID(A2,B2,9999) |
| 3 | FX5200 | 3=MATCH(FALSE,ISERROR(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)+0),0) | FX=LEFT(A3,B3-1) | 5200=MID(A3,B3,1000) | FX 5200=LEFT(A3,B3-1)&" "&MID(A3,B3,9999) |
| 4 | MI6 | 3=MATCH(FALSE,ISERROR(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)+0),0) | MI=LEFT(A4,B4-1) | 6=MID(A4,B4,1000) | MI 6=LEFT(A4,B4-1)&" "&MID(A4,B4,9999) |
| 5 | Size 42 | 6=MATCH(FALSE,ISERROR(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1)+0),0) | Size =LEFT(A5,B5-1) | 42=MID(A5,B5,1000) | Size 42=LEFT(A5,B5-1)&" "&MID(A5,B5,9999) |
MID splits the text into single characters, +0 turns digits into numbers and letters into #VALUE!, and MATCH(FALSE, ISERROR(…)) finds the first character that converted. “Size 42” keeps its space at the end of the text part, so wrap it in TRIM if needed.
If you want the result in one cell with a space between the parts:
{=LEFT(A1,MATCH(FALSE,ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),0)-1)
&" "&
MID(A1,MATCH(FALSE,ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),0),9999)}Separate numbers at the beginning of text
If your cells start with numbers followed by text, you need the position of the first non-numeric character. Here’s the array formula:
{=MATCH(TRUE,ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),0)}| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | First non-digit at | Number part | Text part |
| 2 | 2021y | 5=MATCH(TRUE,ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),0) | 2021=LEFT(A2,B2-1) | y=MID(A2,B2,1000) |
| 3 | 3D | 2=MATCH(TRUE,ISERROR(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)+0),0) | 3=LEFT(A3,B3-1) | D=MID(A3,B3,1000) |
| 4 | 4K | 2=MATCH(TRUE,ISERROR(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)+0),0) | 4=LEFT(A4,B4-1) | K=MID(A4,B4,1000) |
| 5 | 4.2Ghz | 2=MATCH(TRUE,ISERROR(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1)+0),0) | 4=LEFT(A5,B5-1) | .2Ghz=MID(A5,B5,1000) |
Here MATCH looks for the first TRUE — the first character that isn’t a digit. Watch out for the decimal point: in “4.2Ghz” the dot isn’t a digit, so the number part is only “4” and the rest is “.2Ghz”. For decimal numbers, use the regular expression below.
Array formula note: In Excel 365 and Excel 2021, press Enter. In earlier versions, use Ctrl + Shift + Enter.
Split text and numbers with regular expressions
If you don’t know in advance how many text and number segments there are, regular expressions can split them easily.
In Google Sheets
Google Sheets supports regex by default. Here’s a simple formula to split text and numbers:
=TRIM(REGEXREPLACE(A1,"(\d+|\D+)","$1 "))| A | B | |
|---|---|---|
| 1 | Text | Split |
| 2 | 3k2 | 3 k 2=TRIM(REGEXREPLACE(A2,"(\d+|\D+)","$1 ")) |
| 3 | 22st3 | 22 st 3=TRIM(REGEXREPLACE(A3,"(\d+|\D+)","$1 ")) |
| 4 | 1ABC23 | 1 ABC 23=TRIM(REGEXREPLACE(A4,"(\d+|\D+)","$1 ")) |
| 5 | 4.2Ghz | 4 . 2 Ghz=TRIM(REGEXREPLACE(A5,"(\d+|\D+)","$1 ")) |
(\d+|\D+) matches a run of digits or a run of non-digits; "$1 " puts a space after each run, and TRIM removes the last one. Works in Google Sheets and Excel 365. “4.2” is split at the dot — use ([\d.]+|[^\d.]+) to keep decimals together.
In Excel with !SEMTools
Excel 2021 and earlier don’t support regex natively. Excel 365 has the built-in REGEXREPLACE function, and the same formula works there: =TRIM(REGEXREPLACE(A1,"(\d+|\D+)","$1 ")). For older versions, the !SEMTools add-in brings this functionality without limitations. Once installed, you can split text and numbers directly inside Excel with a custom delimiter.
Split text and numbers with !SEMTools in 2 clicks
With !SEMTools, you can split text and numbers directly in the same cell, or output results to a neighboring column, depending on your settings. Just:
- Select the range or cells.
- Click CHANGE → Words → Split text and numbers inside cell.
- Type the delimiter, for example a space.
- Click “OK”.
If the parts are separated by a space, comma or another character, see how to split a cell in Excel and the TEXTSPLIT function.
Need to split text and numbers in Excel?
!SEMTools will do it in seconds and help you automate hundreds of other tasks!