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.
Find the first digit with 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.
Position of the first number with MIN and SEARCH
Here is another array formula, using MIN, SEARCH, CHAR and IFERROR:
{=MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A1),""))}
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | MATCH + ISERR | MIN + SEARCH | Before | From the digit on |
| 2 | Model X5 2026 | 8=MATCH(FALSE,ISERR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)-1),0) | 8=MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A2),"")) | Model X=TRIM(LEFT(A2,B2-1)) | 5 2026=MID(A2,B2,LEN(A2)) |
| 3 | Room 101 | 6=MATCH(FALSE,ISERR(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)-1),0) | 6=MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A3),"")) | Room=TRIM(LEFT(A3,B3-1)) | 101=MID(A3,B3,LEN(A3)) |
| 4 | 2026 report | 1=MATCH(FALSE,ISERR(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)-1),0) | 1=MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A4),"")) | =TRIM(LEFT(A4,B4-1)) | 2026 report=MID(A4,B4,LEN(A4)) |
| 5 | no digits | #N/A=MATCH(FALSE,ISERR(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1)-1),0) | 0=MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A5),"")) | #N/A=TRIM(LEFT(A5,B5-1)) | #N/A=MID(A5,B5,LEN(A5)) |
Both formulas find the same position. With no digits, MATCH returns #N/A and MIN returns 0. The text before the first digit and the rest are LEFT and MID with that position. CHAR(ROW($48:$57)) gives the digits 0–9.
- 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.
First number position in 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
| A | B | C | |
|---|---|---|---|
| 1 | Text | XMATCH (365) | REGEXEXTRACT (365) |
| 2 | Model X5 2026 | 8=XMATCH(TRUE,ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1))) | 8=LEN(REGEXEXTRACT(A2,"^\D*"))+1 |
| 3 | Room 101 | 6=XMATCH(TRUE,ISNUMBER(--MID(A3,SEQUENCE(LEN(A3)),1))) | 6=LEN(REGEXEXTRACT(A3,"^\D*"))+1 |
| 4 | 2026 report | 1=XMATCH(TRUE,ISNUMBER(--MID(A4,SEQUENCE(LEN(A4)),1))) | 1=LEN(REGEXEXTRACT(A4,"^\D*"))+1 |
| 5 | no digits | #N/A=XMATCH(TRUE,ISNUMBER(--MID(A5,SEQUENCE(LEN(A5)),1))) | 10=LEN(REGEXEXTRACT(A5,"^\D*"))+1 |
XMATCH looks for the first character that turns into a number. The regex version measures the non-digits at the start; with no digits at all it returns the text length + 1 (10 for “no digits”), so check the result against LEN.
| A1 | Position of the first digit |
|---|---|
| Order #4512 | 8 |
| SKU-A1024 | 6 |
| 2026 report | 1 |
Split text before and after the first number
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.
Split text and numbers, extract numbers or remove text before the first digit in whole columns right in place, without array formulas.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: which formula to choose
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.