Find and Delete Phrases

[mightymo77]

Hey guys. 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

 

[Micron]

If you can work with a vba solution, then this code on the sheet module could work. You'd have to adapt for the other ranges or repeat it 2 more times because I just noticed you didn't only want this for column C. If you have to put it into a standard module you'd need to adapt to select something other than just the active sheet.

Sub CleanUp()
Dim aryLName() As String
Dim i As Integer, n As Integer

aryLName = Split(",;.;(;);%;&;(HOS);(R);AIF;CFA;CFP;CHFC;CLU;CPA;PFS", ";")
For i = 2 To Range("C1", Range("C1").End(xlDown)).Rows.Count '2 assumes 1st row is header row
   For n = 0 To UBound(aryLName)
      If Cells(i, 3) = aryLName(n) Then Cells(i, 3).Clear
   Next
Next

End Sub

EDIT - I got ahead of myself here and by the time I correct it, it will be too late to edit this post. That will clear the contents, which worked ok for me to test the For Next loops. I'll have to adapt to remove the offending part, not clear the cell contents.
NOTE: I'm also assuming that the characters to be removed will always be at the end of the string.

 

[Micron]

Corrected code below. However, there is a potential problem with my approach. If any of your unwanted values include other unwanted characters, then such combinations must be listed in the array before any of the individual characters that are unwanted characters. For example, because ( and ) were listed before (R) the code would remove ( then later remove ) and leave R, because after removing (, the cell contains R), not (R). So you might find that similar quirks arise with your data.
Anyway, modified as

Sub CleanUp()
Dim aryLName() As String
Dim i As Integer, n As Integer

aryLName = Split(",;.;%;&;(HOS);(R);(;);AIF;CFA;CFP;CHFC;CLU;CPA;PFS;  ;", ";")
For i = 2 To Range("C1", Range("C1").End(xlDown)).Rows.Count
   For n = 0 To UBound(aryLName)
      If InStr(Cells(i, 3), aryLName(n)) > 0 Then
         Cells(i, 3) = Replace(Cells(i, 3), aryLName(n), "")
      End If
   Next
Next

End Sub

Also, I just recalled that you pointed out that there was a value that is just a double space. I added it and it does seem to work. However, keep in mind that it is another combination whose placement in the array might matter.