Remove symbols, Punctuation marks from list

satish78

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

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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,383
Office Version
2016
Platform
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
190
Re: How to remove symbols, Punctuation marks from list

ThanksIstvan
It worked great.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,845
Messages
5,483,271
Members
407,387
Latest member
ajay1303

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top