Remove symbols, Punctuation marks from list

satish78

Board Regular
Joined
Aug 31, 2014
Messages
218
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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

Well-known Member
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
Re: How to remove symbols, Punctuation marks from list

Deleted
 
Last edited:

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
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

name starname finish
May Wen-LiMay Wen Li
Rory Morrison-SmithRory Morrison Smith
Amanee Scott-Stephan, MBAAmanee Scott Stephan

<colgroup><col><col></colgroup><tbody>
</tbody>
 

satish78

Board Regular
Joined
Aug 31, 2014
Messages
218
Re: How to remove symbols, Punctuation marks from list

ThanksIstvan
It worked great.
 

Forum statistics

Threads
1,181,757
Messages
5,931,869
Members
436,805
Latest member
waseem abbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top