Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Extract Numbers from Text (Excel 2002)

    Hi!
    I'm wondering if there is a formula in Excel (or better yet, code in Visual Basic) that will extract a number from a line of text? For example,
    the following text is in cell A1:

    COM 235643 Sales Order

    In most cases the number is in the same spot, therefore I could use the mid formula... however, there is always an exception to the rule, so I thought I'd ask if there was any other kind of formula that could locate the number and extract it into a cell by itself. Also note that in most cases the number is 6 digits, however at times it can be 4 digits... again an exception to the rule. Any ideas would be greatly appreciated.
    Thanks!
    Lana

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extract Numbers from Text (Excel 2002)

    You could create a formula or a macro, but the fastest way may be to make a copy of the column and run the Data | Text-to-Columns wizard on it using the space delimiter.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Numbers from Text (Excel 2002)

    The following function will extract the first number (all digits) it encounters.


    Function getNums(strIn As String) As Double
    Dim i As Integer, outNum As Double
    outNum = 0
    For i = 1 To Len(strIn)
    If IsNumeric(Mid(strIn, i, 1)) Then
    outNum = Val(Mid(strIn, i, 99))
    Exit For
    End If
    Next
    getNums = outNum

    End Function

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extract Numbers from Text (Excel 2002)

    <P ID="edit" class=small>(Edited by JohnBF on 08-Jun-07 08:37. sign option choked on spaces)</P>Nice. Here's an extension that will optionally look for a leading negative sign or open paren:

    Function getNums(strIn As String, Optional boolNeg As Boolean = False) As Double
    ' returns the first sequence of numbers within a cell
    ' spaces, '$' and comma signs are parsed out, decimal separators are included
    ' optional " ,TRUE" argument looks for preceding negative or open paren sign
    Dim intC As Integer, strLeadChar As String

    getNums = 0
    For intC = 1 To Len(strIn)
    If IsNumeric(Mid(strIn, intC, 1)) Then
    getNums = Val(Mid(strIn, intC))
    Exit For
    End If
    Next
    If boolNeg = True Then
    strLeadChar = Mid(strIn, InStr(1, strIn, Left(getNums, 1), vbTextCompare) - 1, 1)
    If strLeadChar = "-" Or strLeadChar = "(" Then getNums = "-" & getNums
    End If
    End Function
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Numbers from Text (Excel 2002)

    When you extract a number from text spaces are ignored. Therefore, another trick worth knowing is to substitute a single space for a number of spaces.

    In cell B1, you could also type the following formula :-
    =VALUE(MID(SUBSTITUTE(A1," "," "),SEARCH(" ",SUBSTITUTE(A1," "," ")),10))

    Regards

    Robert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •