How to remove certain names from a cell?

I need to be able to pull out a few select names from a string of names for each cell in a column. While each cell has a series of names there are only 11 unique names I am looking to post in an additional column and no cell has more than one of these unique names. Is there a formula I can use to do so? Or as an alternative, if a cell has a certain name it remove that name or names and leave the others.

For example:
Current List of names:
Black, Jonny,Rogers, Amber,Brown, Herman
Johnson, BJ,Stewart, Martha,Rogers, Amber
Johnson, BJ,Black, Jonny,Rogers, Amber,Long, Jack
Johnson, BJ,Black,Greeny, Sara,Jonny,Rogers, Amber
Luther, Leigh,Johnson, BJ,Black, Jonny
Johnson, BJ,Black, Jonny,Rogers, Amber,Duck, Donald
Black, Jonny,Rogers, Amber,Brown, Herman
Black, Jonny,Green, Joe
Long, Jack,Johnson, BJ,Black, Jonny,Rogers, Amber
Johnson, BJ,Brown, Herman,Black, Jonny,Rogers, Amber
Johnson, BJ,Rogers, Amber,Jolin, Charlie
Johnson, BJ,Black, Jonny,Rogers, Amber,Brown, Herman
Johnson, BJ,Rogers, Amber,Green, Joe
Johnson, BJ,Black, Jonny,,Jackson, Sam,Rogers, Amber
Johnson, BJ,Black, Jonny,Duck, Donald
Black, Jonny,Rogers, Amber,Luther, Leigh
Johnson, BJ,Black, Jonny,Rogers, Amber,Long, Jack
Green, Joe,Johnson, BJ,Black, Jonny,Rogers, Amber
Johnson, BJ,Black, Jonny,Brown, Herman,Rogers, Amber
Johnson, BJ,Black, Jonny,Rogers, Amber,Stewart, Martha
Johnson, BJ,Black, Jonny,Rogers, Amber,Long, Jack
Black, Jonny,Greeny, Sara


Names I want removed no matter where they are in the cell:
Johnson, BJ
Rogers, Amber
Black, Jonny

Unique names I want remaining in a new column:
Brown, Herman
Stewart, Martha
Greeny, Sara
Duck, Donald
Green, Joe
Jolin, Charlie
Jackson, Sam
Luther, Leigh
Long, Jack

Thanks in advance for helping me save a lot of time!

Sorry Bethany I don't really understand what you mean by retaining the unique names in another column.

But a simple Macro will delete the names that you don't want.

Please Login or Register  to view this content.

If I have interpreted correctly try this non-VBA formula. It works at my end. In B2 enter and fill down:

Formula:

Please Login or Register  to view this content.

Is this what you wanted?

Originally Posted by bethanyjoy10

This formula worked perfectly. Can you please expalin what 25^25,SEARCH stands for so I know for future. Thanks.

I'll give it a try. Search SEARCHes for the existence of items in your "keeper" list ($C$2:$C$10); it returns an index or location number

within the text in question....$A2 in this case. 25^25 is an arbitrarily large number (over the top, really) that insures it will find the

location number no matter how deep it is in the target text nor how large. It locates the first number less than 25^25. That number

also has a position within what is called an array. It too has its own implicit location number.....count its position in the array and that

matches the relative position / row number of a "keeper" item from $C$2:$C$10....which LOOKUP looks up. It then returns

that item.

Does that help?