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,”.”,””), “`”, “”), “_”, “”),”{“,””), “}”, “”)

=RIGHT(I2,10)

=len(H2)

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: