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

[noelb81]

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 Number	Country	Correct Phone Number Format	Country	Country Code
0032 34551857	BE <tbody> </tbody>	3234551857	AT	43
8407717397	AT	438407717397	AU	61
30584778.123	AU	6130584778123	BE	32
+32 40408585	BE	3240408585	BR	55
+385 2409-161	CA	13852409161	CA	1
+1886-9355723	CA	18869355723	CH	41
(06)764,417.441-123	CZ		CZ	420
+41 723 49 45	DE		DE	49
0000492287215651	CA		DK	45

<tbody>
</tbody>

Hoping for your assistance please. This would be much appreciated.

 

[yky]

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