Phone Number Formatting

[bcmk29]

In my spreadsheet, I have a column (D:D) with phone numbers mentioned. I would like to identify and remove the country code. The data isn't consistent and contains various countries phone number in there hence the format and number of characters would differ for each country by desk phone & mobile phone.
For example
US
(415) 555-5555
+14155555555
Singapore
+65 5555 5555
55555555
China
+852 55555555
55555555

Would appreciate any help on this.

 

[Automatrix]

Do you have some independent way of knowing which country a phone number is from, other that looking at the country code?

 

[bcmk29]

Do you have some independent way of knowing which country a phone number is from, other that looking at the country code?

Yes, one of the column has Country details in this file its (I:I).

 

[HongRu]

How about finding the first blank and extract all the right characters ?
In the below code, A1 is the phone number cell.

=MID(A1,FIND(" ",A1)+1,99)

 

[bcmk29]

How about finding the first blank and extract all the right characters ?
In the below code, A1 is the phone number cell.

=MID(A1,FIND(" ",A1)+1,99)

There are phone numbers with country codes and without blanks ex cell D2 in the screenshot.

 

[Automatrix]

Do you have some way of knowing how many digits are in a country's phone number? For example, US phone numbers are always 10 digits. In that case you can use

=RIGHT(A1,[variable number of digits corresponding to the country])

after you have cleared away all dashes, spaces and parentheses.

 

[shinigamilight]

Yes, one of the column has Country details in this file its (I:I).

View attachment 80754

If you have this information all you have to do is copy every country's country code from wiki and paste it into a new sheet, then run a vlookup on this column and get the country code. After that replace all those characters with substitute or something.