A string in Excel, whether you need to extract all extract number from string excel numbers, specific digits, or numbers from mixed alphanumeric data.
Methods to Extract Numbers from a String in Excel
1. Using Excel Functions (TEXT, MID, LEFT, RIGHT, etc.)
Excel does not have a direct function to extract numbers from a string, but you can use a combination of formulas to achieve this.
Example 1: Extracting Numbers Using the TEXTJOIN and MID Functions
If your Excel version supports dynamic array formulas (Excel 365 or Excel 2019), you can use:
excel
Copy
Edit
=TEXTJOIN("",TRUE,IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))
How it works:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1): Extracts each character from the string.
VALUE(...): Converts characters to numbers.
ISNUMBER(...): Checks if each character is a number.
TEXTJOIN("",TRUE,...): Combines all extracted numbers.
Example:
A1 (Input) Formula Output
AB123XY45 12345
X9Y8Z7 987
2. Using the SUBSTITUTE and INDIRECT Functions
If you don’t have dynamic array support, you can use this array formula:
excel
Copy
Edit
=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,""))
Press Ctrl + Shift + Enter after typing the formula to use it as an array formula.
3. Using Excel VBA (Advanced Users)
If you frequently need to extract numbers from strings, VBA provides an efficient solution.
Step 1: Open the VBA Editor
Press ALT + F11 to open the VBA editor.
Click Insert > Module.
Step 2: Add the Following VBA Code
vba
Copy
Edit
Function ExtractNumbers(ByVal str As String) As String
Dim i As Integer
Dim numStr As String
numStr = ""
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
numStr = numStr & Mid(str, i, 1)
End If
Next i
ExtractNumbers = numStr
End Function
Step 3: Use the Custom Function in Excel
After adding the function, use it in a cell:
excel
Copy
Edit
=ExtractNumbers(A1)
This function scans each character in the string and extracts the numbers.
Example:
A1 (Input) Function Output
AB123XY45 12345
98X7Y 987
Use Cases for Extracting Numbers
Cleaning Data – When working with data that includes unwanted text, extracting numbers can help structure information.
Processing Invoice Numbers – Many invoice numbers include letters, but you may need only the numeric part.