How to Extract Text Before Second or Third Instance of a Symbol

For example, I have the following list in cell A1:

“Coffee, cream, sugar, tea”

I am trying to copy all the text before the second or third instance of the comma into an adjacent cell.

To extract the text before the second instance of the comma, use the ff. formula:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1,",","^^",2))-1)

where the 2 represents the comma instance number

The result: Coffee, cream

This entry was posted in Extracting words. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*