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 ?

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

**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.

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

Try this

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

Or

Formula:

Please Login or Register  to view this content.

Or with VBa

Please Login or Register  to view this content.

**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/

**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.

Please Login or Register  to view this content.

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

Regards,
Daniel

Find the position of a substring in a string

LinkBack

Thread Tools

Rate This Thread

Display

Find the position of a substring in a string

Re: Find the position of a substring in a string

Re: Find the position of a substring in a string

Re: Find the position of a substring in a string

Re: Find the position of a substring in a string

Re: Find the position of a substring in a string

Re: Find the position of a substring in a string

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions