Extract 2nd word from a string - varying length, between colons

**Shosi** · 09-11-2012, 08:07 AM

Hi

am a "new" user.

How do I extract the 2nd word from a cell that has varying length and is between colons eg.

eggs:flowers::beans44e
balls:trees::sprouts456gt, etc?

Your help is much appreciated.

**Jakobshavn** · 09-11-2012, 08:17 AM

With your data in A1:
=TRIM(MID(SUBSTITUTE(A$1,":",REPT(" ",999)),(2)*999-998,999))

**Pete_UK** · 09-11-2012, 08:18 AM

Here's one way, with your string in A1:

=RIGHT(LEFT(A1,SEARCH("::",A1)-1),LEN(LEFT(A1,SEARCH("::",A1)-1))-SEARCH(":",A1))

Hope this helps.

Pete

**arlu1201** · 09-11-2012, 08:21 AM

Try this. If the text is in A1, put the formula in B1 -

Formula:

Please Login or Register  to view this content.

**Shosi** · 09-11-2012, 08:23 AM

Thank you very much it is working!!

**Shosi** · 09-11-2012, 08:40 AM

Hi, can I put this in as an Add-Ins because I have to do this every month. If yes, how do I do it?

Thanks in advance.

Extract 2nd word from a string - varying length, between colons

LinkBack

Thread Tools

Rate This Thread

Display

Extract 2nd word from a string - varying length, between colons

Re: Extract 2nd word from a string - varying length, between colons

Re: Extract 2nd word from a string - varying length, between colons

Re: Extract 2nd word from a string - varying length, between colons

Re: Extract 2nd word from a string - varying length, between colons

Re: Extract 2nd word from a string - varying length, between colons

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions