Format a column of phone numbers in multiple ways in Excel 2016

Question

Okay, here's my situation. I have an excel spreadsheet with a list of badly formatted phone numbers from my company's CRM. Most of these are domestic, USA numbers with a +1 country code, some are missing the plus, and some just parentheses. Some are international numbers. Some have extensions. Some of these extensions are ###-###-####x####, some are ###-###-#### ext: ####, etc.

What I need is to reformat these numbers by the following criteria Domestic numbers: All need to be formatted with dashes, and no parentheses: ###-###-#### All extensions need to be separated out into their own column. I can try doing this after the numbers are all formatted properly.

International numbers need to be formatted as a complete string starting with a +, eg +1234567890. Edit Right now, the numbers that are international are typically longer, and not starting with a 1 or +1.

Bonus! Some listings in the sheet have multiple numbers, split by a comma.

Any advice on how to handle this would be greatly appreciated. I'm kinda lost.

Answer 1

One way to do it (and probably not the best) would be to use the next 3-4 columns to clean up the numbers first.

In the first new column, you can clean up your phone number:

• You can easily identify whether there's a + or not with LEFT() function.
• For US numbers, you can remove +1 or 1 with SUBSTITUTE() as well as convert " " into "-".
• If multiple numbers are delimited with ",", you can limit the string to 1 phone number combining LEFT() or MID() with FIND() and looking for "," as the delimiter in FIND()
• Similarly, using IF(), FIND() and LEFT() / MID() / RIGHT(), you can remove any extension.

In the second column, you can do the same for possible 2nd phone numbers that might be listed. To define where to start, you could use something like RIGHT(B2,len(B2)-FIND(",",B2)+1) (assuming your original phone string is in B2) which would only return the the content after the ",".

For the Extensions, you could FIND() "x" or "ext"(using OR() statement if needed) in the original string to see where it starts and isolate them with a RIGHT() statement (I'm assuming they are always at the end, else, you can extract the string between "x" and "," assuming there's another number after using MID()).
(alternatively you can first manually convert all "ext", "Ext.", "exten.", etc to a single string using Excel's Find and Replace feature [CTRL + H])

It's hard to really help much more than that given that your description is rather broad and that, it sounds, the input data is rather poorly organized.