How to Remove Unwanted Characters from Imported Data in Excel

Strip asterisks, currency codes, brackets and dashes from imported data with SUBSTITUTE — and turn the cleaned text into numbers.

You will learn to

• Delete a character by substituting it with ""
• Remove text and convert the rest to a number
• Chain several SUBSTITUTEs to keep only digits

How it works

To delete a character, substitute it with nothing: =SUBSTITUTE(A2,"*","") removes every asterisk. To remove several different characters, nest the calls — each SUBSTITUTE cleans the output of the one inside it: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")","")," ",""),"-","") turns "(555) 201-4567" into "5552014567".

After stripping currency symbols and codes, the result is still text. Wrap it in VALUE to get a real number you can sum: =VALUE(SUBSTITUTE(B2," USD","")). Formulas leave the raw import untouched, so you can re-run the cleanup whenever a new file arrives.

=SUBSTITUTE(A2,"*","")