Find Position Number of word in string

[mohamadmh]

Hi all,

Thank you in advance for your help.

In cell A2 I have: Field3,Field8,Field9,Field10
In cell B2 I would like to get: 2 (the position of the 2 word Field8 in string)

I tried using InStr in UDF
In cell B2 I am using below function: FindWordPosition(A2,"Field8")

result of function in cell B2= 9 instead of desired 2

Function FindWordPostion(Arng As Range, aword As String) As Integer
Dim Position as integer

position = InStr(1, Arng, "ItemB", vbBinaryCompare)

FindWordPostion = position

End function

 

[mohamadmh]

Found a solution. Sharing in case someone else needs similar function.

Function wordPosition(sentence As String, searchWord As String) As Long

Dim words As Variant
Dim i As Long

words = Split(sentence, ",")
For i = LBound(words, 1) To UBound(words, 1)
If words(i) = searchWord Then Exit For
Next i

wordPosition = IIf(i > UBound(words, 1), -1, i + 1)

End Function