Remove symbols, Punctuation marks from list

[satish78]

Hi Friends,

I have a huge list of Names which includes first, middle, last names and along with their titles, education etc.,
for example

Tina DeRubeis van Zyl
May Wen-Li
Rebecca Hodson, CPA, MST
David P. Kraus, Jr.
Heather Findon, CPA
James C. Lawson, JD, CPA, MST
Bruce Feinstein, C.P.A., LL.M. , J.D.
Victoria Colcombe, CFP, ATP
Christopher Abt, CPA
Marc Frutchey, CPA
Cara (Cygan) Matarrese
Cindy Yu, CPA
Andrew P. Brenner, CPA
Jennifer Heiser, CPA
Megan Grohol, CPA
Becky MacFeeters, CPA
Mario Maiorano, CPA
Rory Morrison-Smith
Julia M. Bennetsen
Sarah T.
Kyle J. Bauman, CPA
Bob Ribblett, CPA
Kara Kessinger, CPA, PFS, MTAX, Principal
Amanee Scott-Stephan, MBA
Mark Fahnestock, CPA, PMP
Solomon O'judoh, CPA, MBA
Janice M. Smith, CPA, JD
Christopher Shane, CPA
Larry Schwegel, CPA, MT
Melissa Genovese, CPA, MST


So here I want a VBA/Formula which will remove all occurrences of , . ( ) - ' and for "-" (replace with space) and remove other words like cpa, jd, mt, mst after names

I know it is possible only wit VBA but I am not an expert.
Please help me.

 

[István Hirsch]

Re: How to remove symbols, Punctuation marks from list

Give this a try:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(",",A1&",")-1),"-"," "),"1",""),"(",""),")",""),".","")

 

[snjpverma]

Re: How to remove symbols, Punctuation marks from list

Deleted

 

[liveinhope]

Re: How to remove symbols, Punctuation marks from list

Hi Friends,

I have a huge list of Names which includes first, middle, last names and along with their titles, education etc.,
for example

Tina DeRubeis van Zyl
May Wen-Li
Rebecca Hodson, CPA, MST
David P. Kraus, Jr.
Heather Findon, CPA
James C. Lawson, JD, CPA, MST
Bruce Feinstein, C.P.A., LL.M. , J.D.
Victoria Colcombe, CFP, ATP
Christopher Abt, CPA
Marc Frutchey, CPA
Cara (Cygan) Matarrese
Cindy Yu, CPA
Andrew P. Brenner, CPA
Jennifer Heiser, CPA
Megan Grohol, CPA
Becky MacFeeters, CPA
Mario Maiorano, CPA
Rory Morrison-Smith
Julia M. Bennetsen
Sarah T.
Kyle J. Bauman, CPA
Bob Ribblett, CPA
Kara Kessinger, CPA, PFS, MTAX, Principal
Amanee Scott-Stephan, MBA
Mark Fahnestock, CPA, PMP
Solomon O'judoh, CPA, MBA
Janice M. Smith, CPA, JD
Christopher Shane, CPA
Larry Schwegel, CPA, MT
Melissa Genovese, CPA, MST


So here I want a VBA/Formula which will remove all occurrences of , . ( ) - ' and for "-" (replace with space) and remove other words like cpa, jd, mt, mst after names

I know it is possible only wit VBA but I am not an expert.
Please help me.

Are you sure you want to get rid of hypens. The people People with hyphenated names may not be happy with the result

[TABLE="width: 518"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]name star[/TD]
[TD]name finish[/TD]
[/TR]
[TR]
[TD]May Wen-Li[/TD]
[TD]May Wen Li[/TD]
[/TR]
[TR]
[TD]Rory Morrison-Smith[/TD]
[TD]Rory Morrison Smith[/TD]
[/TR]
[TR]
[TD]Amanee Scott-Stephan, MBA[/TD]
[TD]Amanee Scott Stephan[/TD]
[/TR]
</tbody>[/TABLE]

 

[satish78]

Re: How to remove symbols, Punctuation marks from list

ThanksIstvan
It worked great.

 

[István Hirsch]

Re: How to remove symbols, Punctuation marks from list

You are welcome.