Finding country based on phone numbers

[jtklepp]

I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

Anyone come across this one before?

 

[Shaikh Adnan]

How to extract country codes and country name in excel

Hi, I would like to know how to extract country codes and country name from a list of Phone numbers. The Phone numbers are from different countries and I want to extract the country code and country name.
Country Codes starts from A2 and Country names starts from C2.
Looking for your reply.
Thank you.

Regards,
Adnan

 

[Robert Mika]

Re: How to extract country codes and country name in excel

You should have opened new topic.
Could you post an example of your data?

 

[Shaikh Adnan]

Hi, I would like to know how to extract country codes and country name from a list of Phone numbers. The Phone numbers are from different countries and I want to extract the country code and country name. The Country Codes starts from A2, Country name starts from C2, and Phone numbers starts from G2. Need a formula extract the country code and country name. I'm sorry I do not know Macros. As far as I know we might need to use Nested IF Function and Vlookup Function. Looking forward for your reply.Thank you.​

 

[Robert Mika]

Can you post few examples from each column?
You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[Shaikh Adnan]

Phone Numbers List
US -15417543010
Singapore - 006585555555
Hongkong - 85215985265
Netherlands - 3107354139
India - 919916906464

Country Code for
1. US - 1
2. Singapore - 65
3. Hongkong- 852
4. Netherlands - 31
5. India - 91

These are some random numbers. Thank you

 

[Robert Mika]

So in Column G do you have:

US -15417543010
or
15417543010

and column A has country
US

and column C has codes

1
(the layout before you edit post was different)

 

[AdamL]

Do these work?

[COLOR=#333333]=INDEX($A$2:$A$1000,MATCH(MAX(IF($A$2:$A$1000<>"",(--LEFT(G2,LEN($A$2:$A$1000))=$A$2:$A$1000)*$A$2:$A$1000)),$A$2:$A$1000,0))

[/COLOR][COLOR=#333333]=INDEX($C$2:$C$1000,MATCH(MAX(IF($A$2:$A$1000<>"",(--LEFT(G2,LEN($A$2:$A$1000))=$A$2:$A$1000)*$A$2:$A$1000)),$A$2:$A$1000,0))[/COLOR]

 

[Shaikh Adnan]

Thank you so much for your time!
I have used the above formula before and its working fine. Can I enter the country code in the formula and which will extract the country code from the number in the next column that is in H2 Column?

 

[AdamL]

Can I enter the country code in the formula and which will extract the country code from the number in the next column that is in H2 Column?

I'm sorry, I don't quite understand what you mean by this question.

 

[Shaikh Adnan]

Hi,
Can I have a formula which will extract the country code automatically from the phone numbers? I have the country Country code in A2, Country name in C3 and Phone numbers in D2 and I need to enter the formula in G2. We need to check for 4 digit codes first, if no matches then check 3 digit codes, if no check 2 digit code and else check 1 digit code.