How to extract the number only from a text string in Excel?
When working with Excel, it's common to encounter cells where numbers and text are mixed together—such as "Order 1234" or "Score is 95.6". Extracting just the numeric values from these text strings is often necessary for further analysis, calculations, or reporting. Depending on whether the number is at the start, end, or embedded somewhere within the text, or whether you want only decimals, Excel provides several practical approaches to accomplish this task.
In this guide, we’ll explore a variety of methods to extract numbers from text strings in Excel, covering different scenarios like extracting numbers wherever they appear, handling decimal numbers, and retrieving numbers specifically from the beginning or end of a string. Each method will discuss how it works, its usability, and special tips for effective application.
Extract number from any position from a text string
Extract decimal number only from text string
Extract number from the end of the text string
Extract number from the beginning of the text string
Extract number from any position from a text string
Extracting numbers embedded anywhere within a text string is a frequent need, especially when cleaning data imported from various sources or combining information from multiple sheets. Several practical and efficient solutions exist for this scenario, each having its own ideal use case depending on your version of Excel and familiarity with formulas or add-ins.
♦ Extract number from any position from a text string with Flash Fill
Flash Fill is a quick and handy feature available in Excel 2013 and later that allows you to extract numbers from a text string based on a pattern you demonstrate. This solution doesn't require formulas or macros, making it practical for simple scenarios:
1. In a cell adjacent to your text string, manually type the number as you want it extracted (for example, if A2 contains "Order123A", type "123" in B2).
2. With the active cell below your manual entry, press Ctrl + E (or use Data tab > Flash Fill). Excel will automatically fill down, extracting numbers from similar cells based on your initial example.
This method is excellent for one-off, small-to-medium datasets where the structure is consistent. Note that Flash Fill relies on recognizing patterns—if your data is highly inconsistent or contains mixed numeric/text order, it may not extract as intended. If Flash Fill does not auto-detect, check if it is enabled via File > Options > Advanced > Automatically Flash Fill.
♦ Extract number from any position from a text string with formulas
Using formulas is a versatile way to extract numbers from text strings. This method uses either built-in or dynamic array functions, and is especially helpful if you need a solution that automatically updates when source data changes.
Select a blank cell where you want to display the extracted number. Then, enter one of the formulas below depending on your Excel version. After inputting the formula, drag the fill handle down to apply it to other cells as needed. See the illustration below for reference:
● All Excel versions:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")This formula is compatible with all modern Excel versions and is a reliable choice if you are not using Excel 365 or Excel 2021. Please note that formulas using multiple functions may affect performance in large datasets. Also, verify that text strings contain at least one numeric character; otherwise, the formula may return a blank or an error.
● Excel 365 or Excel2021 and later versions:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))This formula takes advantage of new dynamic array functions, greatly simplifying the extraction process for users of Office 365 or Excel 2021 and newer. It handles both simple and more complex alphanumeric mixes efficiently. Ensure you are using the correct version, or the formula may not work as expected.
Tip: When using formulas with mixed or negative numbers, or numbers with special symbols, review extracted results to ensure accuracy. Also, formulas may not recognize decimal points by default, so if you need to extract decimal numbers, please refer to the dedicated section below. If you get an unexpected result or an error, double-check your cell references and formula syntax.
♦ Extract number from any position from a text string with Kutools for Excel
Kutools for Excel provides a straightforward method to extract numbers from any position within a text string. Especially suitable for users who prefer an interactive interface over formulas or need to process a large range without manual effort, this method saves time and streamlines tasks by eliminating the need to memorize or troubleshoot formulas.
- Click Kutools > Text > Extract Text as shown below.
- In the Extract Text dialog box:
(1.) Select the list of cells containing your text strings;
(2.) Choose the Extract the number option, which ensures only numeric portions are captured;
(3.) If you want extracted numbers to update automatically with source data changes, check Insert as a formula (otherwise, results will be static text);
(4.) Click OK.
- When prompted, select a cell for the output. Make sure you select a blank area with enough space for the results to prevent overwriting existing data.
- Click OK. The numbers from each selected cell will be extracted instantly.
This approach is user-friendly and reduces the risk of formula errors or misapplied cell references. However, note that Kutools is an add-in and is not available in the standard Excel installation. It is well-suited for batch operations and for users looking to avoid complex function syntax or VBA.
If the numbers are not extracted as you expected, double-check if there is mixed formatting (like hidden characters or non-standard numerals) in your original text. Also, always keep a backup copy of your worksheet before making bulk changes.
♦ Extract number from any position from a text string with VBA code
For more advanced users or specific situations where formulas and add-ins are not sufficient, VBA offers a flexible approach for extracting numbers from text strings in Excel. This method is highly customizable and especially useful for automating repetitive tasks or processing non-standard datasets.
- Open the VBA editor by pressing Alt + F11. In the Microsoft Visual Basic for Applications window, click Insert > Module. In the new module window, paste the following code:
- VBA code: Extract number only from text string:
Sub ExtrNumbersFromRange() Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Then Exit Sub Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8) If TypeName(xRRg) = "Nothing" Then Exit Sub xI = 0 strNumber = "" For Each xRg In xDRg xI = xI + 1 nCellLength = Len(xRg) For xNumber = 1 To nCellLength If IsNumeric(Mid(xRg, xNumber, 1)) Then strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Next xRg End Sub - After inserting the code, press F5 or click the Run button. A prompt box will appear asking you to select the range of text strings to process.
- Once you've selected your range, click OK. A second prompt will ask you to specify the starting cell for the output.
- Click OK again. All numbers embedded within the original selection will be extracted and placed in the output area you have specified.
Advantages of the VBA method include handling large datasets efficiently and the ability to customize the behavior (such as extracting only the first number, handling decimals, or excluding certain symbols) if you have basic VBA knowledge. Precautions: VBA macros cannot be undone and may overwrite data if the chosen output range overlaps your original data, so always test on a backup file first. Ensure macros are enabled in Excel.
Extract decimal number only from a text string
Sometimes, your text strings may contain numbers with decimal points (such as "weight:15.25kg"). If you specifically need to extract decimal numbers—including the floating point—standard formulas may not capture these correctly. The solutions below are designed to handle such situations:
The following formulas can help extract decimal numbers from text strings efficiently. Enter the appropriate formula in a blank cell adjacent to your data, then use the fill handle to copy it to additional rows as needed.
● All Excel versions:
=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))This formula supports most versions of Excel and is crafted to identify decimal points as part of a number during extraction. Double-check your data to ensure there are no extraneous periods in your text. If decimals are not extracted as expected, review the original string for non-standard characters or formatting issues.
● Excel 365:
=REGEXEXTRACT(A2,"[\d.]+")With Excel 365, dynamic arrays can more intelligently process and return extracted decimal numbers from complex text strings. Enter the formula in the target cell, press Enter, and then extend the formula down if needed.
Tip: If you repeatedly receive zeros or errors instead of numbers, make sure your data contains valid decimal numbers and the cell references are correct. If your numbers can include both negative values and decimals, formulas may require adjustments.
Extract number from the end of the text string
When numbers always appear at the end of your text strings (for example, "221_ProductID_446" or "923Ticket#2021"), you may want to extract only the trailing number ("446" or "2021"). The formula provided here is designed for precisely that scenario, ensuring that only numeric sequences at the end of the text are retrieved.
Copy and paste the following formula into a blank cell. For older Excel versions, confirm the formula with Ctrl + Shift + Enter to create an array formula. For newer Excel versions with dynamic arrays, just press Enter:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))Drag the fill handle down to apply this formula to other rows. This method is best for consistent formats where only the last number in each string is required, and ignores numbers that appear in the middle or at the beginning.
Note: If your data mix contains spaces, special symbols, or variations in position, adjust the formula or use a more general method as described above. Always check that the output is as expected, especially if your text ends with non-numeric characters that resemble numbers.
Extract number from the beginning of the text string
If you need to extract only numbers appearing at the very start of your text strings, a dedicated formula will help you isolate just this portion.
Place the formula in a blank target cell, and for older Excel versions, use Ctrl + Shift + Enter to create an array formula (in Excel 365/2021, simply press Enter):
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)Then, drag the formula down for all rows requiring extraction. This approach will only return numbers from the start of the string—even if more numbers appear elsewhere, only the leading numeric part is captured.
Tip: If the first character in your string is not numeric, this formula may return a blank or an error. Check and clean your original data as needed for the best results.
Extracting numbers from text strings is a common and often crucial task in Excel data preparation. The right method depends on your data structure, Excel version, and comfort with formulas or add-ins. If your scenario doesn't fit the above solutions, consider using a combination of methods or exploring other dedicated add-ins for advanced needs. Checking your extracted results for inconsistencies, keeping backup copies, and starting with smaller data samples can help avoid typical issues.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in