Separate numbers from text in Excel cells

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)

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

Array formula note: In Excel 365 and Excel 2021, press Enter. In earlier versions, use Ctrl + Shift + Enter.

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

In Excel with !SEMTools

Excel doesn’t support regex natively, but 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:

1. Select the range or cells.
2. Run the procedure.
3. Choose a delimiter.
4. Click “OK”.

This post is also available in RU.