If you’ve ever had a column full of messy text like “Invoice123” or “Order: 4589 units” and just wanted the numbers out of it, you’re not alone. Excel doesn’t have one single “extract numbers” button, but there are a few solid ways to get the job done depending on your version of Excel and how your text is structured.
Method 1: Using Flash Fill (Fastest, No Formulas)
If you’re on Excel 2013 or later, Flash Fill is probably your quickest option. It learns patterns from what you type.
- In the cell next to your text, manually type the number you want extracted from the first row.
- Press Enter, then go to the next row.
- Press Ctrl + E (or go to Data > Flash Fill).
- Excel will automatically fill in the rest based on the pattern it detected.
This works great when the pattern is consistent, like numbers always appearing after a colon or at the end of a string.
Method 2: Using TEXTJOIN with MID (For Any Text, Excel 2019+/365)
If you have Microsoft 365 or Excel 2019+, this array formula pulls out every digit from a string, no matter where it sits:
=TEXTJOIN("",TRUE,IFERROR(MID(A1,SEQUENCE(LEN(A1)),1)*1,""))Here’s what it’s doing behind the scenes: it checks every single character in the cell, tries to treat it as a number, and if it works, keeps it. If not, it throws it out. Then it joins all the surviving digits back together.
=VALUE(TEXTJOIN(...))Method 3: Using an Older-Style Array Formula (Works in Older Excel)
If TEXTJOIN and SEQUENCE aren’t available to you, this classic array formula does the same job, just written the old-fashioned way:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1)))),1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10^LEN(A1))Yes, it looks intimidating. It’s one of those formulas nobody memorizes, they just copy it and adjust the cell reference. It returns the digits from the string as an actual number, in the order they appeared.
Method 4: Extracting Numbers from a Specific Position
Sometimes you don’t need every number in the whole string, just the ones in a predictable spot. If your text always follows a pattern like “ABC-1234”, you can use simpler functions.
Numbers at the end of the string
=RIGHT(A1,LEN(A1)-FIND("-",A1))Numbers at the start of the string
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)This second one looks strange, but it’s basically hunting for the position of the first digit and grabbing everything before it flips into non-numeric territory… actually, grabbing everything up to that first digit’s position minus one won’t get the number itself. If you actually want the leading number, you’re better off using this instead:
=LEFT(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)Enter this one as an array formula with Ctrl + Shift + Enter in older Excel versions.
Method 5: Power Query (Best for Large Datasets)
If you’re dealing with hundreds or thousands of rows, doing this with Power Query is far more manageable and doesn’t slow your workbook down with heavy array formulas.
- Select your data and go to Data > From Table/Range.
- In Power Query Editor, select your text column.
- Go to Add Column > Custom Column.
- Use this formula:
Text.Select([YourColumn], {"0".."9"})This grabs every digit character from the string and ignores everything else. It’s clean, fast, and doesn’t choke on large datasets the way some array formulas do.
Which Method Should You Actually Use?
| Situation | Best Method |
|---|---|
| Quick one-time job, consistent pattern | Flash Fill |
| Need a formula that updates automatically | TEXTJOIN + MID (365/2019+) |
| Older Excel, no dynamic arrays | Classic array formula |
| Numbers in a fixed position | LEFT/RIGHT with FIND |
| Large dataset, ongoing process | Power Query |
Honestly, for most people just cleaning up a small list, Flash Fill will save you the most time. But if you’re building something that needs to update automatically as new data comes in, it’s worth taking the few extra minutes to set up the formula-based or Power Query approach instead.
