Find the position of a substring in a string

**nemo66ro** · 10-21-2012, 05:21 AM

Hello guys,

I have a long string with different (and unique) names of people. In this string I am interested to find the position of a specific substring not by characters but by words...Let say my string has 1000 words and I am looking for the "Jeremy Wade" word position. Suppose this index is 705 (I have 704 words before).
Is it possible to find this index without any loop thru each value of this string ?

Dim myString as String
myString = "John Scott Mary Fox Terry Dyke......Jeremy Wade......"

Thank you in advance.

**Marcol** · 10-21-2012, 05:52 AM

Try Instr()
If the string has no match then zero is returned, otherwise it returns the position of the first match found, 37 in this example.

myString = "John Scott Mary Fox Terry Dyke......Jeremy Wade......"
posn = instr(mystring,"Jeremy Wade")

**Richard Buttrey** · 10-21-2012, 07:18 AM

Hi,

In your example is Jeremy Wade the 7th word, or are you looking for the result of 4? i.e. The 4th name.

**nemo66ro** · 10-21-2012, 12:53 PM

Hi Richard,

Jeremy Wade is the 705 th word....So, I am interested to find the position of the word in the entire string. Do you think is it possible? Thanks all.

**Richard Buttrey** · 10-21-2012, 02:23 PM

Originally Posted by nemo66ro

Hi Richard,

Jeremy Wade is the 705 th word....So, I am interested to find the position of the word in the entire string. Do you think is it possible? Thanks all.

Hi,

That isn't what I was asking. I said what's the position in your 'example' string not your actual string. I'm trying to establish what you regard as a word/name/

**Marcol** · 10-21-2012, 01:54 PM

Try this

Function CountWords(rng As Range, str2find As String)
  Dim posn As Long
  Dim strTemp As String
  
  posn = InStr(rng, str2find)
  strTemp = Left(rng, posn - 1)
  
  CountWords = Len(strTemp) - Len(Replace(strTemp, " ", "")) + 1
End Function

Use in Excel like so, where A2 is your string and B2 is the string to find, Drag Down as required.

Formula:

=CountWords(A2,$B$1)

Or

Formula:

=CountWords(A2,"Jeremy Wade")

Or with VBa

WordNo = CountWords(ActiveSheet.Range(A2),"Jeremy Wade")

**nemo66ro** · 10-22-2012, 01:21 AM

Hello Marcol,
Yes, your function is getting exactly what I want. A beautiful code line that will make my life easier.
For those who want this function in a vb standard I have re-written it below.

Function CountWords(bigString As String, subString As String)
      Dim posn As Long, strTemp As String, merge As String
    
      posn = InStr(1, bigString, subString)
      strTemp = Left$(bigString, posn - 1)
      merge = Replace(strTemp, " ", vbNullString)
      CountWords = Len(strTemp) - Len(merge) + 1
End Function

Thank you very much Marcol and thank you Richard too for your interest in my issue.

Regards,
Daniel