I have a worksheet that contains thousands of cells, each with data that are separated by commas. For instance, the first three cells could be:
Active, Tim's Client, Texas Clinic, XYZ Insurance
Active, Mark's Client, Florida Clinic, ABC Insurance
Active, LMN Insurance, Michigan Clinic, Sally's Client
I need to search the cells and return only the text "Tim's Client" or "Mark's Client" or "Sally's Client" into a cell by itself so I can setup a pivot table based on each employees clients. The employees aren't always in the same sequence within the cell, so using "text to columns" won't produce a consistent list.
I saw one forum that suggested referencing a list of employee's by using the following formula ("things" is the name of their list in the example) to return the first match, since there's never a case where two employees would be listed in the same cell:
{=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))}
I couldn't get this to work though. Is there a better way, or a change that I need to make to this formula?
Bookmarks