How to extract number only from text string in Excel?
When working with Excel, you might encounter situations where you need to extract numbers from text strings. Whether the numbers are at the beginning, end, or embedded within the text, Excel provides various methods to achieve this. This guide will explore various methods to handle different scenarios, including extracting decimal numbers, numbers from specific positions, or numbers from the start or end of a string.
Extract number from any position from text string
Extract decimal number only from text string
Extract number from the end of text string
Extract number from the beginning of text string
Extract number from any position from text string
To help you extract numbers from any position within a text string, we’ll introduce several practical and efficient methods.
♦ Extract number from any position from text string with formulas
The following formulas can help you to extract the numbers from the text strings, please do as this:
Select a blank cell where you want to output the extracted number, then type any of the below formula, then drag the formula to other cells. See screenshot:
● 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),"")
● Excel 365 or Excel 2021 and later versions:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))
♦ Extract number from any position from text string with formulas
Kutools for Excel offers a quick and user-friendly way to extract numbers from text strings without remembering any formula, helping you save time and streamline your workflow. Here’s how to do it.
- Click Kutools > Text > Extract Text, see screenshot:
- In the Extract Text dialog box, do the following operations:
(1.) Select the data list you want to extract numbers;
(2.) Choose Extract the number option;
(3.) Check Insert as a formula option as you need;
(4.) Finally, click OK button.
- In the following prompt box, select a cell to output the extracted result, see screenshot:
- Click OK, all numbers from each cell are extracted at once. See screenshot:
♦ Extract number from any position from text string with VBA code
Here is a VBA code which also can do you a favor, please do as follows:
- Hold down the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the following code in the Module Window.
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
- And then, press F5 key to run this code, and a prompt box is popped out to remind selecting the text range you want to use, see screenshot:
- Then, click OK, another prompt box is following, please select a cell to output the result, see screenshot:
- At last, click OK button, and all numbers in the selected cells have been extracted at once.
Extract decimal number only from text string
If the text strings which including some decimal numbers in your worksheet, how could you extract only the decimal numbers from the text strings?
The below formula can help you to extract the decimal numbers from the text strings quickly and easily.
Copy and paste any of the following formula to a blank cell, and then fill handle down to the cells that you want to contain this formula, all the decimal numbers are extracted from the text strings
● 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"))))
● Excel 365:
=REGEXEXTRACT(A2,"[\d.]+")
Extract number from the end of text string
When working with text strings in Excel, you may need to extract numbers located at the end of the string. To isolate numbers at the end of a string, the following formula can help you.
Copy and paste the below formula into a blank cell, press Ctrl + Shift + Enter to get the first result:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))
Then, drag the fill handle down to fill this formula to other cells. This formula extracts numbers exclusively from the end of the text string, ignoring any numbers that appear at the beginning or in the middle. See screenshot:
Extract number from the beginning of text string
To extract numbers from the beginning of a text string, please use the following formula.
Copy and paste the below formula into a blank cell, press Ctrl + Shift + Enter to get the first result:
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)
Then, drag the fill handle down to fill this formula to other cells, only the starting number is extracted. See screenshot:
Extracting numbers from text strings is a common task in Excel, and there are several effective methods to achieve this, depending on your needs and the complexity of your data. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
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!