Extracting Numbers from Strings in Excel

While working with Excel, there are instances where we need to extract only the numerical part from a given string. This post shares a method to achieve this. The following article provides detailed instructions on how to extract numbers from a given string in Excel.

Two methods to extract numbers:

- Case 1: Extracting numbers from strings and retrieving positive values.

- Case 2: Extracting numbers from strings and retrieving both positive and negative values.

1. Extracting numbers from strings and retrieving positive values

Step 1: Open the Excel file you need to extract numbers from -> Press the key combination Alt + F11 -> the VBA window appears -> Go to the Insert tab -> Module.

Step 2: In the command window, enter the following code snippet:

Function ExtractNumber(rCell As Range)
Dim lCount As Long
Dim sText As String
Dim lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, lCount, 1)) Then
lNum = Mid(sText, lCount, 1) & lNum
End If
Next lCount
ExtractNumber = CLng(lNum)
End Function

Note: Please remember the name of this function as it will be needed when returning to the Excel file.

Step 3: After entering the code, select Save (since it contains a macro, when saving, a dialog box will appear, simply click OK) -> Return to the Excel file and locate the function as shown in the image:

Step 4: Copy the formula for the remaining cells resulting in:

2. Extracting numbers from strings and retrieving both positive and negative values

Step 1: Open the Excel file you need to extract numbers from -> Press the key combination Alt + F11 -> the VBA window appears -> Go to the Insert tab -> Module.

Step 2: In the command window, enter the following code:

Private Function SuperTrim(TheStr As String)
Dim Temp As String, DoubleSpace As String
DoubleSpace = Chr(32) & Chr(32)
Temp = Trim(TheStr)
Temp = Replace(Temp, DoubleSpace, Chr(32))
Do Until InStr(Temp, DoubleSpace) = 0
Temp = Replace(Temp, DoubleSpace, Chr(32))
Loop
SuperTrim = Temp
End Function

Public Function Extract_Numbers(strText As String)
Dim strText_1 As String
Dim subText() As String, numbers() As Double
Dim i As Integer, j As Integer, k As Integer, m As Integer
strText = SuperTrim(strText)
subText = Split(strText, ' ')
For i = 0 To UBound(subText)
For j = 1 To Len(subText(i))
k = 0
If IsNumeric(Mid(subText(i), j, 1)) _
Or (Mid(subText(i), j, 1) = '-' And IsNumeric(Mid(subText(i), j + 1, 1))) Then
k = j
Exit For
End If
Next j
If k <> 0 Then
m = m + 1
strText_1 = Val(Mid(subText(i), k))
ReDim Preserve numbers(1 To m) As Double
numbers(m) = strText_1
End If
Next i
If index > 0 And index <= m Then
Extract_Numbers = numbers(m)
Else
Extract_Numbers = ''
End If
End Function

Note: Remember the function name 'Split_numbers().'

Step 3: Click on Save, simply choose OK. Return to the Excel file, select the Split_numbers() function as shown in the image:

Step 4: The Split_numbers() function only has one parameter, which is the cell position to split.

Step 5: Copy the formula for the remaining cells to get the result:

Thus, both negative and positive values are successfully separated. Even negative signs at the beginning of the cell are captured.

Note: These functions only apply to sequences containing a continuous string of numerical characters. If the data consists of multiple sequences of numerical characters separated by alphabetic characters, the returned value will be the first sequence of numerical characters.

Wishing you all success!