This is a formula for use by users that are familiar with using formulas in Excel. If you are not such a user, be careful and backup before you start.
We need to convert a column of variously formatted phone numbers to a common format. This can be difficult if the source has several formats that Excel doesn’t like. Here we will develop a formula to take care of the most common issues and get us where we want to be. The end result is this. You may need to change the red to match your sheet. They just point to the source cell.
=TEXT(IF(B2=””,””,SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(–MID(B2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)),”[<=9999999999]1+(000) 000-0000;#+(###) ###-####”)
This is where I started. I found this at Mr. Excel.
=SUMPRODUCT(MID(0&A24,LARGE(INDEX(ISNUMBER(–MID(A24,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
This formula is where I started because the source had various formats already. This takes out all the non numeric characters. I’m not going to go into detail about how this formula does it, just believe me when I tell you it does it.
First I’ll change a couple of places and assume the phone number is in column B and the formula is in column A. Also the sheet has a header row so we are in Row 2.
=SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(–MID(B2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
We jus need to add a few checks.
First, I the value is zero, replace with null. This would logically use the formula as a test but this would get us to a very long formula before we get started good so I cheated and just check for blank source.
=IF(B2=””,””,SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(–MID(B2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))
Now, lets apply the format. This is where we can change it to what we want. Below I have used the standard Excel Phone Number format. It assumes either a 7 or 10 digit number. It could be tweaked for your particular format.
=TEXT(IF(B2=””,””,SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(–MID(B2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)),”[<=9999999]###-####;(###) ###-####”)
This formula obviously fails if the source has invalid data to begin with and doesn’t allow for the country code. I’m going to tweak is some to allow for included country code. Since I’m dealing with US numbers, I assume the country code is always 1.
=TEXT(IF(B2=””,””,SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(–MID(B2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)),”[<=9999999999]1+(000) 000-0000;#+(###) ###-####”)
Under many circumstances, you would copy the formatted cells and past special (values) to the source and delete the formulas
I’m going to leave you for now with this as my final solution. I look forward to any questions about added functionality.
References:
http://stackoverflow.com/questions/15754501/combining-duplicate-entries-with-unique-data-in-excel
http://office.microsoft.com/en-us/excel-help/display-numbers-as-phone-numbers-HA010342436.aspx
http://www.mrexcel.com/forum/excel-questions/459758-how-do-i-strip-out-all-non-numeric-characters.html