# Thread: Parse a text String (xl2003)

1. ## 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. ## 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. ## 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. ## 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. ## 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
Match: \$ 4500.00 Max: \$ 3500.00
Match: \$ 350.34 Max: \$ 350.33
Match: \$ 24000.34 Max: \$ 24045.10

Can you help me here?

6. ## 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
•