Main Page ┬╗ MS Excel Functions ┬╗ Extract Number From Text


Extract Number From Text


Adapted from http://www.ozgrid.com/VBA/ExtractNum.htm
By using this function, you can extract numeric part of alphanumeric text. According to your needs, you may extract numbers with decimal sign and/or negative sign.

=Extract_Number_From_Text(Value, Include_Decimal, Include_Negative)

At the figure below, you can see using of parameters;
samples of extracting number from text
Here is the code:

sample.txt
Function Extract_Number_From_Text(Value As Range, Optional Include_Decimal As Boolean, _
                                  Optional Include_Negative As Boolean) As Double
Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String, lNum As String
Dim vVal, vVal2
'Extracts numeric values from a cell containing text and numbers.
sText = Value
If Include_Decimal = True And Include_Negative = True Then
    strNeg = "-"
    strDec = "."
ElseIf Include_Decimal = True And Include_Negative = False Then
    strNeg = vbNullString
    strDec = "."
ElseIf Include_Decimal = False And Include_Negative = True Then
    strNeg = "-"
    strDec = vbNullString
End If
iLoop = Len(sText)
For iCount = iLoop To 1 Step -1
vVal = Mid(sText, iCount, 1)
If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
    i = i + 1
    lNum = Mid(sText, iCount, 1) & lNum
If IsNumeric(lNum) Then
If CDbl(lNum) < 0 Then Exit For
Else
lNum = Replace(lNum, Left(lNum, 1), "", , 1)
End If
End If
If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
Next iCount
Extract_Number_From_Text = CDbl(lNum)
End Function

See also: Clean Cell
You can download running sample from below link.
Download example file


´╗┐