Extract Number From Text String

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

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 special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft