# help on formula to get the clean format country code + phone number

#### noelb81

##### New Member
Hi Everyone,

Hope someone can help on this.
The main goal is to have make the phone numbers made to the format country code + phone number
Example:
A2 = (Phone Number) = 678.121-1234(5674)
B2 = (Country Abbreviation) = US
C2 = 167812112345674

D2 = COUNTRY ABBREVIATION
E2 =COUNTRY CODE

The problems are
1) the A list has a lot of symbols that needs to be removed
2) the Country code need to be looked up on the data of D AND E
3) Some of A has the country code, and some does not. Need to make sure that the country code is not added if there is already a country code in A

I am not good at this so hope I am able to explain it clearly. Below is an example.
I have thousands of these numbers that needs to be corrected so hope someone can help on what formula I should use to get Correct Phone Number Format like the first few of the example below:

Phone NumberCountryCorrect Phone Number FormatCountryCountry Code
0032 34551857
 BE

<tbody>
</tbody>
3234551857AT43
8407717397AT438407717397AU61
30584778.123AU6130584778123BE32
+32 40408585BE3240408585BR55
+385 2409-161CA13852409161CA1
+1886-9355723CA18869355723CH41
(06)764,417.441-123CZCZ420
+41 723 49 45DEDE49

<tbody>
</tbody>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### yky

##### Well-known Member
What is the correct phone number format for (06)764,417.441-123 and 0000492287215651?
What are the range of country and country code?

Replies
16
Views
494
Replies
0
Views
657
Replies
1
Views
116
Replies
42
Views
3K
Replies
12
Views
356

1,191,553
Messages
5,987,240
Members
440,086
Latest member
Mahi786

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back