Phone Number Format based on country

[underin]

Hi Guys,

I have an excel with a column of phone numbers in varying formats.

I need a system to clean the numbers based on country code which is stored in another column.

For instance lets say the phone number column contains:

4921030012722
+49 (2103) 00 12722
12103001282
+1 (210) 300 1282

Lets assume the country code for the first two numbers is Germany and US for the latter two.

The formatted numbers should be +xx (xxxx) xx xxxxx for Germany with the first two digits representing the country code and +x (xxx) xxx xxxx for US.

Any help is highly appreciated.

 

[j_unsuitable]

Hey,

not really an answer to your question but i think thats far more complicated than you think.
You want to display the area code in parenthesis and you use Germany in your example, but our area codes dont have a set number of digits. It can vary between three and, I THINK, 5 digits (including the leading zero, which is removed when adding the country code) and the phone number without the area code doesnt have a fixed number of digits either.
Like my number at work would be +xx (xxx) xxxxxxx and my number at home would be +xx (xxxx) xxxxx and the number of my neighbour could be +xx (xxxx) xxxxxx and the number of a person in Berlin could be +xx (xx) xxxxxxx