To split numbers from text in Excel, use Flash Fill (Excel 2013 and later) for a quick one-time fix, or use formulas like LEFT, RIGHT, MID, and FIND for a dynamic solution that updates when your data changes. For example, with text like “ABC123” in cell A1, =VALUE(RIGHT(A1, LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789"))+1)) extracts the number 123.
Why You Might Need to Split Numbers From Text
Excel is often used to clean up imported data. You might have a column of product codes like “SKU-4521”, addresses like “123 Main St”, or log entries like “Error 404 at 12:30”. In many cases, you need the numeric part for calculations (e.g., summing quantities) or the text part for sorting (e.g., by category). Manually retyping hundreds of rows is error-prone and time-consuming. Understanding the built-in tools and formulas can save you hours and reduce mistakes.
Method 1: Flash Fill (Easiest for One-Time Tasks)
Flash Fill (Ctrl+E on Windows, Cmd+E on Mac) recognizes patterns in your data and fills the rest of a column automatically. It’s perfect when you don’t need the result to update if the original data changes later.
- In a new column next to your data, type the desired result for the first cell manually. For “ABC123”, type
123in the adjacent cell. - Press Enter, then start typing the next cell’s expected result (e.g., for “XYZ456”, type
456). Excel will often show a preview of the remaining cells in light gray. - Press Ctrl+E (Windows) or Cmd+E (Mac) to accept the Flash Fill suggestion.
If Flash Fill doesn’t trigger automatically, you can also go to Data > Flash Fill in the ribbon. Note: Flash Fill does not create formulas—it creates static values. If your source data changes, the split results won’t update. Also, Flash Fill works best when the pattern is consistent (e.g., letters then numbers, or numbers at the end).
Method 2: Formulas for Dynamic Splitting
If you need the results to update automatically when the original text changes, use formulas. The key is to locate the position of the first digit (or the first non-digit) and then extract the appropriate substring.
Assume your text is in cell A1. To extract the leading number (e.g., from “123ABC”):
=VALUE(LEFT(A1, MATCH(FALSE, ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)*1), 0)-1))
This array formula (press Ctrl+Shift+Enter in older Excel; in Excel 365 it works normally) checks each character, finds the first non-number, and returns the numeric prefix. For “123ABC”, it returns 123.
To extract the trailing number (e.g., from “ABC123”):
=VALUE(RIGHT(A1, LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789"))+1))
This works by finding the position of the first digit in the string (the MIN(FIND(...)) part) and then taking everything to the right of that. It returns 123 for “ABC123”.
To extract the text part (e.g., “ABC” from “ABC123”):
=LEFT(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789"))-1)
These formulas assume the numbers are either at the beginning or the end. If numbers are in the middle (e.g., “AB12CD”), you’ll need more complex formulas or a different approach.
Method 3: Text to Columns (For Fixed Patterns)
Text to Columns (Data > Text to Columns) is useful when your data has a consistent delimiter, like a space, comma, or hyphen. For example, if your text is “Order 1234” and you want “Order” and “1234” in separate columns:
- Select the column with your data.
- Go to Data > Text to Columns.
- Choose “Delimited” and click Next.
- Check the delimiter that matches your data (e.g., Space) and click Next.
- Choose the destination cell and click Finish.
This method splits the text into separate columns based on the delimiter. However, if the delimiter is not consistent (e.g., sometimes spaces, sometimes not), this won’t work well. Also, the results are static—they don’t update if the original data changes.
Method 4: Power Query (For Complex or Repeated Cleaning)
Power Query (Data > Get Data > From Table/Range) is a powerful tool for cleaning data, especially if you need to repeat the process on new data regularly. You can split columns by a delimiter or by number of characters, and even use custom formulas.
- Select your data range and go to Data > From Table/Range (this creates an Excel table).
- In the Power Query editor, select the column you want to split.
- Go to Home > Split Column > By Delimiter (or By Number of Characters).
- Choose the delimiter and how to split (e.g., at each occurrence).
- Click Close & Load to put the results back into Excel.
Power Query creates a connection that can be refreshed when the source data changes (Data > Refresh All). This is ideal for recurring data cleaning tasks.
Choosing the Right Method
Here’s a quick guide to help you decide:
- Flash Fill: Best for one-off, simple patterns, and when you don’t need automatic updates.
- Formulas: Best when you need dynamic results that update when the original data changes, and the pattern is consistent (numbers at start or end).
- Text to Columns: Best for fixed delimiters like spaces or commas, and when you want a quick static split.
- Power Query: Best for complex patterns, repeated cleaning tasks, or when you want to automate the process.
Common Problems and Solutions
One common issue is that numbers might be mixed with text in unpredictable ways, like “AB12CD34”. The simple formulas above won’t handle that. In such cases, you might need to use a more advanced formula with MID and ROW to extract all digits, or use Power Query with a custom column that uses a regular expression (Power Query doesn’t support regex natively, but you can use a custom function).
Another issue is that the extracted numbers might be treated as text. To convert them to actual numbers, wrap your formula with VALUE() or multiply by 1. For example, =VALUE(RIGHT(A1,2)).
Also, be aware that FIND is case-sensitive, but that doesn’t matter for numbers. If your text contains no digits, the MIN(FIND(...)) formula will return an error. You can handle that with IFERROR to return a blank or a custom message.
Frequently Asked Questions
Can I split numbers from text without formulas? Yes, Flash Fill and Text to Columns are two methods that don’t require formulas. They are static, though—if the original data changes, you’ll need to repeat the process.
How do I split numbers that are in the middle of text? For example, “AB12CD”. The simple LEFT/RIGHT formulas won’t work. You can use a formula that extracts all digits: =TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)*1), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), "")) entered as an array formula (Ctrl+Shift+Enter in older Excel). This returns “12”. For the text parts, you’d need to remove the digits, which is more complex.
Why is my formula returning a #VALUE! error? This often happens when the text contains no digits, or when you use array formulas without pressing Ctrl+Shift+Enter in older versions of Excel. Use IFERROR to handle cases with no digits.
Does Flash Fill work on Mac? Yes, Flash Fill is available in Excel for Mac 2016 and later. The shortcut is Cmd+E.
Can I split numbers and text using a macro? Yes, you can write a VBA macro to automate this. For example, you could loop through cells and use the Val function to extract numbers. However, macros cannot be undone with Ctrl+Z, so always save a copy of your workbook before running one.
Is there a way to split numbers from text in Excel Online? Yes, Flash Fill and Text to Columns are available in Excel for the web, but Power Query is not. Formulas work the same way, but array formulas (like the ones shown) may require you to press Enter normally, as Excel 365 handles dynamic arrays automatically.
Related
- Excel Power Query Prevent Refresh: How to Stop It
- Excel Power Query Slow to Refresh? Fixes That Work
- Excel Power Query: Merge Rows from Columns or Tables
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- ChatGPT Excel Data Analysis: How to Get Useful ResultsYes. ChatGPT can analyze Excel data when you upload a supported workbook, such as an .xlsx file, or…
- Excel Workbook Not Opening? Causes and Fixes for WindowsIf an Excel workbook will not open, first try opening Excel in Safe Mode, then use File >…
- How to Insert a Citation in Word OnlineTo insert a citation in Word Online, open the document and check the References tab for Insert Citation.…
- Excel Shortcut to Paste Formatting and Keep DataIn Excel for Microsoft 365 on Windows, copy the formatted cell, select the destination, then press Ctrl+Alt+V, press…
- How to Merge Two Word Documents to Compare ChangesTo compare two Word documents, open Word for Microsoft 365 on Windows and choose Review > Compare >…
- How to Delete a Page in Word for the Web (Browser)To delete a page in Word for the web, select the text or other content on that page…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.