find a key word in a string (Leo Asked)

[UnicLeo]

Hi
Is there anyone direct me on how I can build a function to return the
next word in a string to the specified keyword.
for example I need to know an invoice number or a chek number in text
like this;
MyString = "Recd CN 125458 from Customer for inv 45889"
CheckNo= Myfunction_FindStr(SearchIn=MyString, KeyWord ="CN")
? CheckNo >>"125458" returned.
Here I know that all check numbers are pre-fixed with "CN" and I can
extracts all check numbers from this text.
I tried this code, but it had bugs that it does not return exactly the
next word to the keyword and it sometimes includes spaces before or
after the returned string.

Option Explicit

'================================================
'there are two bugs in this function
' 1) it does not return exactly the next word from the key, as it
intends to return
'2)the length of returned string is not exactly correct

Public Function FindStr(strFindIN As String, strFindWhat As String,
Optional lngLength As Long = 0)
Dim lngStartPoint As Long
lngStartPoint = InStr(1, strFindIN, strFindWhat, vbTextCompare)
If lngStartPoint = 0 Then
FindStr = ""
Exit Function
End If
If lngLength = 0 Then
lngLength = InStr(lngStartPoint + Len(strFindWhat) + 1, strFindIN,
" ", vbTextCompare) - lngStartPoint - Len(strFindWhat) - 1
End If
FindStr = Mid(strFindIN, lngStartPoint + Len(strFindWhat) + 1,
lngLength)
End Function


please help
Leo

 

[Gary''s Student]

Take a look at this:

Function get_next_string(bigstring As String, littlestring As String) As
String
get_next_string = ""
s = Split(bigstring, " ")
For i = LBound(s) To UBound(s)-1
If s(i) = littlestring Then
get_next_string = s(i + 1)
Exit Function
End If
Next
End Function


Tested with:

Sub test()
MsgBox (get_next_string("Recd CN 125458 from Customer for inv 45889", "CN"))
End Sub

The technique is to take the "MyString" and split it up using the space as a
separator. Then look for a match to the little string. If a match is found,
return the next substring.

 

[Leo]

thanks a lot, it realy helped
actually I added a parameter "ExactWord" to allow me to search for both
"inv" and "invoice" if user has a habit of entering both.

Public Function NextWord(InString As String, FirstWord As String, Optional
ExactWord As Boolean = True) As String
Dim WordArr() As String ' keeping all words in an array
Dim i As Integer ' loop counter

WordArr = Split(InString)
NextWord = ""
If ExactWord = True Then
For i = LBound(WordArr) To UBound(WordArr) - 1
If UCase(FirstWord) = UCase(WordArr(i)) Then
NextWord = WordArr(i + 1)
Exit For
End If
Next i
Else
For i = LBound(WordArr) To UBound(WordArr) - 1
If InStr(1, WordArr(i), FirstWord, vbTextCompare) Then
NextWord = WordArr(i + 1)
Exit For
End If
Next i
End If

End Function

by the way,
do you know what the usage of "Compare" option in "Split" Function?!
what kind of comparision does this function do?

 

[Gary''s Student]

From Excel 2003 VBA Help:

compare Optional. Numeric value indicating the kind of comparison to use
when evaluating substrings. See Settings section for values.



Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption –1 Performs a comparison using the setting of the Option
Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on
information in your database.

I have never used this option and have never seen it used.