Find next word to the current word in a string(Leo)

[Leo]

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 in my accounting database;
MyString = "Recd CN 125458 from Customer for inv 45889"
CheckNo= Myfunction_FindStr(SearchIn=MyString, KeyWord ="CN")
? CheckNo >>"125458" returned.
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

 

[Ed]

Hi Leo,

For short strings, an alternative to finding positions and lengths and so
forth is to split the string into an array, find the array element that
contains the keyword, and pick up the value of the next array element.

Something like ...

Function FindStr(FindIN As String, Keyword As String)
Dim StringAsArray() As String
Dim i As Integer

StringAsArray() = Split(FindIN)

For i = 0 To UBound(StringAsArray)
If UCase(StringAsArray(i)) = UCase(Keyword) Then
FindStr = StringAsArray(i + 1)
End If
Next

End Function

And to test it ...

Sub Test()
Dim MyString As String
Dim CheckNo As String
Dim InvNo As String

MyString = "Recd CN 125458 from Customer for inv 45889"

CheckNo = FindStr(FindIN:=MyString, Keyword:="cn")
MsgBox CheckNo

InvNo = FindStr(FindIN:=MyString, Keyword:="inv")
MsgBox InvNo
End Sub

Regards.

Ed