# Excel VBA Video Training / EXCEL DASHBOARD REPORTS # Extract Number From Text String

Back to: Excel Custom Function/Formulas . 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. See Also Sort Alphanumeric Text

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

''''''''''''''''''''''''''''''''''''''''''

'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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.