Search text string

[gripper]

Hi guys,

Hoping somebody can help. I have a multi-column worksheet and in column "B" I have long text strings within the several hundred rows in which I want to extract some data and place that extracted date in to column "H"

There is a consistent pattern with the start and end point.

The start point of the string is after "in " and the end point is a comma ",". I want what is in the middle which is either is 1-5 words.

Example: John Smith in Detroit, Michigan. In this case I would want "Detroit" copied to column "H"

These text strings are a bit longer then this and of different lengths so it needs to use these delimiters to mark the beginning and end of the data.

Any help would be greatly appreciated.

Thank you

 

[Scott Huish]

=trim(right(substitute(left(a1,find(",",a1&",")-1)," ",rept(" ",255)),255))

 

[Vidar]

Or
=MID(B2,SEARCH("in ",B2)+3,SEARCH(",",B2)-(SEARCH("in ",B2)+3))

 

[István Hirsch]

Or
=MID(B2,SEARCH("in ",B2)+3,SEARCH(",",B2)-(SEARCH("in ",B2)+3))

I would search for " in " to avoid finding Irwin, Edwin etc.

 

[gripper]

Wow, the "=Mid" formula worked perfectly.

Thank you so much.