Excel Phone Number Cleaner

Phone numbers from a list contains characters that are not standardized. There are unwanted characters like period, dashes or hyphens, parenthesis, braces, brackets, sapces, slashes, etc.

One of the things I do is to reprocess those columns using the Excel formulas below so that what I get is a clean 10-digit phone number.

Just copy and paste it to a blank column on your excel sheet then change the given cell to your actual cell.

=Substitute(substitute(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2, “-“, “”), “(“, “”), “)”,””), ” “, “”), “*”, “”), “+”, “”), “=”, “”), “/”, “”)

=Substitute(substitute(Substitute(SUBSTITUTE(SUBSTITUTE(K2,”.”,””), “`”, “”), “_”, “”),”{“,””), “}”, “”)




Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: