Finding country based on phone numbers

J

jtklepp

New Member
Joined
Dec 5, 2008
Messages
22
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Welcome to the Board.

I think first we would need to see some examples of your data ... you mention in *most* cases the country code is specified in first 3 chars but this obviously implies this is not always the case... try and provide as varied a sample of strings as you can... I'm sure then you will get a solution.
 
Upvote 0
Here are some samples (the only relevant column is the one with the phone number - I have put XXXX at the end of the number as this is real data):

3069328xxxx
44773281xxxx
1615730xxxx
6140532xxxx
97254790xxxx

The sample numbers contain data from Greece (+30), UK (+44), US/Canada (+1), Australia (+61) and Israel (+972). I do have a complete table of country codes so I know that 972 is Israel and 44 is the UK. So whatevery my macro does, it can look up on this table to find the matching country. The problem of course is whether there are 1,2 or 3 digits to check.
 
Upvote 0
So, I'm guessing you are saying that the 1615730 number is a US/Canada number, right ? But is there also a code 16 for a different country ?

I don't know how country codes work. I guess it's conceivable that anything starting with a 1 is for the US/Canada, anything starting with a 9 is a 3 digit code, and anything else is a 2 digit code, in which case you can work something out relatively easily. Is that how it works ?
 
Upvote 0
Country codes are more complex than Gerald's guess.
http://www.accommodationz.co.nz/dialingcodes.html
Seems to me that there's only 2 single digit codes (1 for US and Canada and 7 for Russian Federation and Kazakhstan.

The two digit codes tend to be countries that have been around a while or are bigger players.

Three digit codes are smaller or relatively new countries and four digits seem to be used for regional subgroups or tiny countries.

Rich (BB code): 
<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=190 border=0 x:str><COLGROUP><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 111pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=148 height=17>USA</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 32pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=42 x:num>1</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=190 border=0 x:str><COLGROUP><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 111pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=148 height=17>Bahamas</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 32pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=42 x:num>1242</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Barbados</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1246</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Anguilla</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1264</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Antiga</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1268</TD></TR></TBODY></TABLE>
....
<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=190 border=0 x:str><COLGROUP><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 111pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=148 height=17>Spain</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 32pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=42 x:num>34</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Gibraltar</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>350</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Portugal</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>351</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Luxembourg</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>352</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Ireland</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>353</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Iceland</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>354</TD></TR></TBODY></TABLE>
....
<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=190 border=0 x:str><COLGROUP><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 111pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=148 height=17>Italy</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 32pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=42 x:num>39</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Romania</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Switzeland</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>41</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Czech Republic</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>420</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Slovakia</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>421</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Austria</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>43</TD></TR></TBODY></TABLE>

This one will be tricky!
 
Upvote 0
So maybe something like (Ctrl+Shift+Enter):

=INDEX($A$1:$A$1000,MATCH(MAX(IF($B$1:$B$1000<>"",(--LEFT(C1,LEN($B$1:$B$1000))=$B$1:$B$1000)*($B$1:$B$1000))),$B$1:$B$1000,0))

assuming lookup table is A1:B1000 (countries A1:A1000, numerical country codes B1:B1000), and phone number to lookup is in C1.

So with reference to teylyn's post, a phone number of 1246264909 will return Barbados, not USA.
 
Upvote 0
This article tries to solve it in PHP with SQL: http://www.phpfreaks.com/forums/index.php?topic=224789.0

Could you solve it by:
1. Running each number against a look up table for all countries with 1 or 3 to see if you find a match? If you don't find a match, assume there are 2 digits and then
2. Run the same number against a table and look for match with 2 digits?

I realize this means that each number needs to go through the lookup table 3 times, and it will take a looooong time to run if the list is long, but still...
 
Upvote 0
Surely you need to check for 4 digit codes first, if no matches then check 3 digit codes, and so on.

This could be handled reasonably easily using nested IF and VLOOKUP statements, without any VBA.
 
Upvote 0
Turned out to be a bit complicated, but solved it the following way:

1. Found a list of country codes and put into the worksheet
2. Split the numbers in 4,3,2 and 1 using =VALUE(LEFT($D2,4)) and so on
3. Used IF and VLOOKUP to check. The formula is
=IF(ISNA(VLOOKUP(J3,CountryCodes,1,FALSE)),IF(ISNA(VLOOKUP(K3,CountryCodes,1,FALSE)),IF(ISNA(VLOOKUP(L3,CountryCodes,1,FALSE)),IF(ISNA(VLOOKUP(M3,1,FALSE)),VLOOKUP(M3,CountryCodes,2),"Unknown"),VLOOKUP(L3,CountryCodes,2)),VLOOKUP(K3,CountryCodes,2)),VLOOKUP(J3,CountryCodes,2))

where 4, 3, 2, and 1 left most numbers are in columns J-M.

Thanks for everyone that contributed!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Clean up List of Phone Numbers
2
Replies
11
Views
533
alansidman
A
B
  • Question
Phone Number Formatting
Replies
6
Views
694
shinigamilight
S
M
  • Question
Hi, Have a problem with Power Automate and decided to use Excel VBA
Replies
3
Views
218
ides315
I
D
  • Question
Fastest way to filter a Listobject directly to an array
Replies
0
Views
533
dumitrudan608
D
G
  • Question
Seperate all contact valid numbers from cluttered text in Excel through VBA
Replies
3
Views
793
nemmi69
nemmi69

Forum statistics

Threads
1,214,661
Messages
6,120,796
Members
448,994
Latest member
rohitsomani

Share this page

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
Back
Top