How to Extract Numbers from Text in Excel 2016
Question details
The user needs a way to separate and extract numeric values from alphanumeric text strings within an Excel 2016 worksheet.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Cleaning or processing mixed data where numeric information needs to be separated from surrounding text for calculation or reporting.
- Observed behavior
- Excel 2016 lacks a single native built-in function to extract numbers from any arbitrary text pattern, requiring workarounds like Flash Fill, formulas, or VBA.
Ensure your data does not contain trailing spaces that might interfere with extraction, and determine if you need to extract whole numbers, decimals, or negative values before choosing a method.
Use Flash Fill for Consistent Data Patterns
Flash Fill is the quickest and easiest method when the numbers you want to extract follow a clear, predictable pattern across the column.
This built-in feature uses machine learning to identify the pattern you demonstrate and applies it to the rest of the data. It works best when all cells share the same text structure.
In an empty column immediately next to your data, type the exact number you want to extract from the first cell and press Enter.
Select the cell directly below your typed number, navigate to the Data tab on the ribbon, and click 'Flash Fill' (or press the keyboard shortcut Ctrl + E).
Check the automatically filled column to ensure Excel correctly identified the pattern. If it missed some variations, manually correct one of the errors to teach the tool.
Create a Custom VBA Function (UDF)
For irregular text strings where Flash Fill fails, a custom VBA macro can loop through characters and pull out only the numeric digits.
Utilize Power Query for Advanced Data Cleanup
Power Query is an excellent tool for repeatable and automated data transformations, including splitting text and numbers from complex data sets.
Extract Data Easily with WPS Office
WPS Office offers intuitive data processing tools, including a powerful Flash Fill equivalent and advanced formula capabilities to cleanly separate numbers from text. It is fully compatible with Microsoft Excel files, allowing you to clean your data efficiently.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your mixed text data.
- 2. Provide a sample extraction: Type the desired extracted number in the adjacent cell to show the software what you need.
- 3. Use Flash Fill: Press Ctrl+E or go to the Data tab and select Flash Fill to automatically populate the rest of the column with extracted numbers.
Frequently Asked Questions
Can I extract numbers with decimals or negative signs using standard formulas?
Standard formulas can be quite complex when handling decimals and negative signs, often requiring nested functions like MID, MIN, FIND, and LEN. For consistent accuracy with non-integer numbers, using VBA or Power Query is usually recommended.
Why did Flash Fill extract the wrong numbers?
Flash Fill relies on recognizing patterns from your sample data. If your text strings have highly inconsistent formats (e.g., varying numbers of spaces or mixed text structures), Flash Fill might misinterpret the pattern. Providing two or three manual examples before activating Flash Fill can improve its accuracy.
Are there any third-party add-ins for extracting text?
Yes, there are custom Excel add-ins available online that provide dedicated custom functions (like NumsLeft or NumsRight) to separate numbers. However, always ensure you download add-ins and their documentation from trusted sources to avoid security risks and malware.
