Extracting certain text from text string

**Cicada** · 07-12-2012, 11:07 AM

Hello all,

I have a list of urls on sheet 1 of my workbook that always appear like so.

HTML Code:

These appear from cell A1 to A"(The number varies). I would like to extract the names only from these strings and have them placed in order on sheet 3. So in example 3 moscow-flight (would it be possible to replace the - with a space) example 2 beachland vieri etc. I have looked at split and InStr but can't get it to work. They will always look like the above with all the text that comes before the names the same. Any ideas. Thankyou.

Regards
Dan

**StevenM** · 07-12-2012, 11:18 AM

See: Cicada_Book1.xlsm
Run: CopyNames2WkSheet

The code is:

Please Login or Register  to view this content.

**singerj6** · 07-12-2012, 11:27 AM

You could do this very quickly with Text-to-Columns, "/" delimited, and then Find and Replace "-" with " "

**K m** · 07-12-2012, 11:29 AM

This is a complicated formula but this works. In cell C2 put this formula:
=SUBSTITUTE(MID(B2,FIND("/",B2,FIND("/",B2,FIND("/",B2,FIND("/",B2,FIND(".",B2))+1)+1)+1)+1,FIND("/",B2,FIND("/",B2,FIND("/",B2,FIND("/",B2,FIND("/",B2,FIND(".",B2))+1)+1)+1)+1)-FIND("/",B2,FIND("/",B2,FIND("/",B2,FIND("/",B2,FIND(".",B2))+1)+1)+1)-1),"-"," ")

Copy the formula down

Kirk