Find the position of the first number in a cell

We’ve already learned how to find the position of the first letter in a cell. The formula for the position of the first digit works on a similar principle: it turns the string into an array of characters and checks each one to see whether it’s a digit.

Why you’d need it: to split text and numbers into separate cells, or to remove the text before the first digit.

Option 1: MATCH and ISERR

{=MATCH(FALSE,ISERR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)-1),0)}

How it works: MID splits the text into characters, and 1 is subtracted from each (any number works). For non-digit characters, the subtraction returns an error, and the ISERR function turns the array into TRUE for errors and FALSE for digits. MATCH returns the position of the first FALSE, that is, the first digit.

Like all array formulas in Excel 2019 and earlier, it’s entered with Ctrl+Shift+Enter.

Option 2: MIN and SEARCH

Here is another array formula, using MIN, SEARCH, CHAR and IFERROR:

{=MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A1),""))}

• each of the 10 digit characters (codes 48 to 57) is searched in the string; SEARCH returns its position or an error;
• IFERROR replaces the errors with empty values so that MIN doesn’t fail;
• MIN returns the smallest position: that’s the first digit.

This one is a bit more demanding on resources (text functions are “more expensive” than math), so the first option is recommended for large ranges. If there are no digits, it returns 0, while the first one returns #N/A.

Option 3: Excel 365

In Excel 365, SEQUENCE replaces ROW(INDIRECT(…)), and XMATCH looks for the first TRUE:

=XMATCH(TRUE,ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1)))

And with regular expressions, the position is the length of everything before the first digit plus 1:

=LEN(REGEXEXTRACT(A1,"^\D*"))+1

Using the formulas

Once you have the position, the rest is easy. The text before the first digit is =TRIM(LEFT(A1,position-1)), and everything from the first digit on is =MID(A1,position,LEN(A1)). More about checking whether a cell contains digits at all in the article on how to find numbers in cells.

Summary

I hope you found what you were looking for on this page and were able to find the position of the first number in your cells. MATCH with ISERR is the fastest classic formula, MIN with SEARCH is the easiest to read, and in Excel 365 XMATCH or a regex is the shortest.