Formula to remove unwanted characters

**adam2308** · 04-28-2009, 07:20 AM

I am looking for a formula that will remove any unwanted characters in a cell.

For example i may a word or string of words with "-" , " ' " or "," in them and i would like to have these removed.

Thanks,
Adam.

**DonkeyOte** · 04-28-2009, 07:22 AM

Depending on the number of unique characters you want removed you can embed Substitutes

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"'",""),",","")

**JONvdHeyden** · 04-28-2009, 08:21 AM

Or you can use a UDF. Copy this to a module in your workbook:

Please Login or Register  to view this content.

List all characters you want to remove in a new table.

=substitutelist(A1,H2:H4)

Where A1 contains the original text string, and H2:H4 house characters that you want to remove. Change ranges to suit.

**Ryan_Bernal** · 01-17-2013, 04:31 AM

Use CTRL+F then delete those character.

**dinesh_ltjd** · 01-17-2013, 04:38 AM

If you dont want to create Function in VBA or use any formule then you can just seleact all the names and then use replace (CTRL+H) and replace these characters with ""