Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parse a text String (xl2003)

    I export to Excel a report from a mainframe application.
    There is a column with rows of text strings in the same column - each cell similar to this:
    Match:$4500.00 Max: $3500.00
    Match:$350.34 Max: $350.33
    Match:$24000.34 Max:$24045.10
    I would like to extract the two numbers from the cell and subtract the second text number from the first text number.
    The result being in a cell in the same row as the original string.
    The text string is not always in the same row or column when extracted

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parse a text String (xl2003)

    If you have such a text string in cell A1, the formula
    <code>
    =MID(A1,FIND(":",A1)+1,FIND(" ",A1)-FIND(":",A1))-MID(A1,FIND(":",A1,FIND(" ",A1)+1)+1,100)
    </code>
    will extract and subtract the two numbers. This formula can be filled down.
    If you don't know in which column the strings are, it's hard to tell what the best solution would be.

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse a text String (xl2003)

    Wow Hans . That's fantastic. Is it possible to turn the formula into a function expanding on something like this:

    Function Get_Word(text_string As String, nth_word) As String
    Dim lWordCount As Long

    With Application.WorksheetFunction
    lWordCount = Len(text_string) - Len(.Substitute(text_string, " ", "")) + 1

    If IsNumeric(nth_word) Then
    nth_word = nth_word - 1
    Get_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
    .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _
    .Find(" ", Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
    .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256)) - 2)
    ElseIf nth_word = "First" Then
    Get_Word = Left(text_string, .Find(" ", text_string) - 1)
    ElseIf nth_word = "Last" Then
    Get_Word = Mid(.Substitute(text_string, " ", "^", Len(text_string) - _
    Len(.Substitute(text_string, " ", ""))), .Find("^", .Substitute(text_string, " ", "^", _
    Len(text_string) - Len(.Substitute(text_string, " ", "")))) + 1, 256)
    End If
    End With

    End Function

    Thanks so much

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parse a text String (xl2003)

    Here is a function that will return the difference of the two amounts:

    Function ExtractDiff(ByVal text_string As String) As Double
    Dim intPos1 As Integer
    Dim intPos2 As Integer
    Dim intPos3 As Integer
    Dim str1 As String
    Dim str2 As String
    intPos1 = InStr(text_string, "$")
    intPos2 = InStr(intPos1 + 1, text_string, " ")
    intPos3 = InStr(intPos2 + 1, text_string, "$")
    str1 = Trim(Mid(text_string, intPos1 + 1, intPos2 - intPos1))
    str2 = Trim(Mid(text_string, intPos3 + 1))
    ExtractDiff = Val(str1) - Val(str2)
    End Function

    Use like this:

    =ExtractDiff(A1)

    Note: there is no need to use WorksheetFunction in the Get_Word function, since VBA has its own string manipulation functions. Here is a shorter and more efficient version of Get_Word:

    Function Get_Word(ByVal text_string As String, ByVal nth_word) As String
    Dim arr
    arr = Split(text_string)
    If UCase(nth_word) = "FIRST" Then
    nth_word = 1
    ElseIf UCase(nth_word) = "LAST" Then
    nth_word = UBound(arr) + 1
    End If
    Get_Word = arr(nth_word - 1)
    End Function

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse a text String (xl2003)

    Hans ;
    The revisions to GetWord sure does make it more efficient and faster. Thanks for bringing the code up to date.

    On the ExtractDiff Function, I think I need your help to tweak the final function. There is a space between the $ and the following number and the function does not seem to account for that. Probably because the example I gave to start with did not have the space in it to begin with . My apologies
    It should have read:
    Match: $ 4500.00 Max: $ 3500.00
    Match: $ 350.34 Max: $ 350.33
    Match: $ 24000.34 Max: $ 24045.10

    Can you help me here?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parse a text String (xl2003)

    I had relied on the examples you provided and assumed that the first space was that before the word Max. Try this version instead:

    Function ExtractDiff(ByVal text_string As String) As Double
    Dim intPos1 As Integer
    Dim intPos2 As Integer
    Dim intPos3 As Integer
    Dim str1 As String
    Dim str2 As String
    intPos1 = InStr(text_string, "$")
    intPos2 = InStr(intPos1 + 1, text_string, "Max")
    intPos3 = InStr(intPos2 + 1, text_string, "$")
    str1 = Trim(Mid(text_string, intPos1 + 1, intPos2 - intPos1 - 1))
    str2 = Trim(Mid(text_string, intPos3 + 1))
    ExtractDiff = Val(str1) - Val(str2)
    End Function

Posting Permissions

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