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

noelb81

New Member
Joined
Nov 24, 2016
Messages
1
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
0000492287215651CADK45

<tbody>
</tbody>


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

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
Joined
Jun 7, 2011
Messages
1,881
Office Version
  1. 2010
Platform
  1. Windows
What is the correct phone number format for (06)764,417.441-123 and 0000492287215651?
What are the range of country and country code?
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top