Find and Delete Phrases

Hello everyone. What would be the easiest way to find the below phrases in columns C (First Name), D (Middle Name), E (Last Name) and delete them? For example suppose along those three columns I had:
Column C - John
Column D - J.
Column E - Doe AIF, CFA(R)

But I needed it to be:
Column C - John
Column D - J
Column E - Doe

Is there a single formula that can do this? Note the very last one is a double space:

,
.
(
)
%
&
(HOS)
(R)
AIF
CFA
CFP
CHFC
CLU
CPA
PFS

Maybe you can use UDF like this:

Formula:

Please Login or Register  to view this content.

	A	B	C
1	(HOS)	()John J. Doe AIF, CFA(R)	John J Doe
2	(R)		=MyNewWord(A1:A16,B1)
3	AIF
4	CFA
5	CFP
6	CHFC
7	CLU
8	CPA
9	PFS
10
11	,
12	.
13	(
14	)
15	%
16	&

or through columns. But loop through 3 columns (I do not know how many rows you expect to check) and search in values 15 strings to replace take a while.

Please Login or Register  to view this content.

or somebody find something faster.

or...


A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet, with representative samples, an explanation and some expected results.

Hi. Provide SEVERAL representative examples (not just one) on an Excel sheet please!!!

OK I have attached an example on my first post above.

Originally Posted by mightymo77

I edited my first post to be more clear. The columns C, D, E are actually three columns of First, Middle, Last Name, it comes this way when exported from the database. My goal is to find those phrases listed and delete them to clean up the names.

It does not change anything.
You can use UDF somewhere (other sheet called 'ClearData' or other columns to get clear data).
Or looping through C/D/E and check. Of course can be auto declared as Range("C1:E" & lastRow).
But as I said simple loop takes a while if you got lots of rows to check.

EDIT: Change this line into

Please Login or Register  to view this content.