Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    extracting numbers from within text (XL97 or 2)

    I want to be able to pick out numbers from a text string but the text is of arbitrary length and the numbers do not occur at a fixed point in the string, nor do the numbers all begin with the same value, for example:

    <table border=1><td>text</td><td>required number</td><td>>0.1</td><td>0.1</td><td>> 3.2</td><td>3.2</td><td>0.1mg/kg</td><td>0.1</td><td>greater than 0.1 mg/kg</td><td>0.1</td></table>

    Any suggestions?

    stuck

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extracting numbers from within text (XL97 or 2)

    Try this user defined function:

    <pre>Option Explicit

    Function ExtractNumber(oCell As Range) As Double
    Dim sStr As String
    Dim sChar As String
    Dim sNum As String
    Dim bStart As Boolean
    Dim lCount As Long
    sStr = oCell.Value
    For lCount = 1 To Len(sStr)
    sChar = Mid(sStr, lCount, 1)
    If (bStart = False And sChar = "-") Then
    sNum = sChar
    End If
    If sChar Like "[0-9]" Then
    bStart = True
    sNum = sNum & sChar
    ElseIf bStart = True And sChar = Application.International(xlDecimalSeparator) Then
    sNum = sNum & sChar
    ElseIf bStart = False And sNum = "-" Then
    sNum = ""
    End If
    Next
    ExtractNumber = CDbl(sNum)
    End Function
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: extracting numbers from within text (XL97 or 2)

    This array (confirm with ctrl-shift-enter) will work if the only period is a decimal
    =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIREC T("1:100")),1)),0),SUM(IF((1-ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))+1*(MI D(A1,ROW(INDIRECT("1:100")),1)="."),1)))

    It is a modification of Bob Umlas' technique from Array Formulas to allow for the number to be within the string, not just the end.

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: extracting numbers from within text (XL97 or 2)

    An alternate to Jan's UDF, which lets VB do most of the "heavy-lifting" is:

    <pre>Function ExtractNumber(vValue)
    Dim x As Integer
    For x = 1 To Len(vValue)
    If Val(Mid(vValue, x)) <> 0 Then
    ExtractNumber = Val(Mid(vValue, x))
    Exit Function
    End If
    Next
    End Function</pre>


    If the text has no numbers a 0 is given.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extracting numbers from within text (XL97 or 2

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

    Clever stuff, either of the UDFs will sort my problem. Jan Karel's ignores a minus sign and just gives the absolute value, Steve's includes the minus sign but if the minus sign has been used as a hyphen, e.g. "my text - 0.1" then it still sees it as a negative number. Neither of these situations should occur but as my data is entered by 'users'...

    An extra period, other than the dec. point, is more likely so I'll pass on the array formula idea.

    Many thanks once again.

    (un)stuck

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extracting numbers from within text (XL97 or 2

    There was a mistake in my code, I had:

    ElseIf bStart = False And sNum = "-" Then


    this is what it should have been:

    ElseIf bStart = False And sNum <> "-" Then
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extracting numbers from within text (XL97 or 2

    err, I hardly dare question the great Jan Karel but are you sure? When I replace your orignal line with the new line the function fails at the new line.

    stuck

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extracting numbers from within text (XL97 or 2

    Well, it seemed to work for me (see attached)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extracting numbers from within text (XL97 or 2

    I knew I shouldn't have questioned it...

    The problem was I had pasted the correction in from my email client and it (Mozilla) had renderd the < symbol as < The VBE then complained about the ; It's OK now I've fixed that.

    (un)stuck

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extracting numbers from within text (XL97 or 2

    I suspected something like it, which is why I attached the workbook I cooked up.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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