Extract Number From Text String
Category: [Excel] Demo Available
Extract Number From Text String
Got any Excel/VBA Questions? Free Excel Help
Get Numbers From Alphanumeric Text in Excel
This UDF will extract the numeric portion from a alphanumeric Text String.
The Code
Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_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
Dim lNum As String
Dim vVal, vVal2
''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com
'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell
If Take_decimal = True And Take_negative = True Then
strNeg = "-" 'Negative Sign MUST be before 1st number.
strDec = "."
ElseIf Take_decimal = True And Take_negative = False Then
strNeg = vbNullString
strDec = "."
ElseIf Take_decimal = False And Take_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
ExtractNumber = CDbl(lNum)
End Function
To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function (Shift+F3).
It should be noted that the ExtractNumber Function has 2 optional arguments (Take_decimal and Take_negative). These are both False if omitted. See the table below to see how alphanumeric text is treated.
| Alphanumeric Text | Formula | Result |
| a-bg-12909- | =ExtractNumber(A1,,TRUE) | -12909 |
| a-bg-12909- | =ExtractNumber(A2) | 12909 |
| a.a1.2... | =ExtractNumber(A3,TRUE) | 1.2 |
| a.a1.2... | =ExtractNumber(A4) | 12 |
| a.a-1.2…. | =ExtractNumber(A5,TRUE,TRUE) | -1.2 |
| abg1290.11 | =ExtractNumber(A6,TRUE) | 1290.11 |
| abg129013Agt | =ExtractNumber(A7) | 129013 |
| abg129012 | =ExtractNumber(A8) | 129013 |
See also:
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.