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

Extract Number From Text

Adapted from
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:

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
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