Excel Formula To Extract Country From Phone Number - 2550

Is there an Excel formula that can return the country name from the telephone number?

This video shows you how. To make your life easier, add this worksheet with five named ranges already in it:

CountryCodeLookups.xlsx

Microsoft Excel Workbook

1drv.ms

Transcript of the video:

Well, I love a good challenge. Get the country name from the phone number.
So, hey, I got tagged on LinkedIn. Someone named Y.S.
Had this Excel challenge, had this list of phone numbers, so they all start with a plus sign.
And they identify the country code and the country and said. "Any formula that could use a lookup table to get the country out of the phone number," And tagged a few people that he knew in Excel.
And thanks to Daniel who tagged me and said, "Let's see if Bill can help. He loves these challenges and questions.
Maybe you'll make a video". And yes, here it is, the video.
All right, so the first thing, I took the data that Yitzie posted and I said, "Is this going to be easy?
Can we just subtract the phone number from the end?" In the United States, phone numbers are all 10 digits, right?
But I did the math, so figure out the length, take out the length without the plus sign, and then the country length I know it's 44, 41, 350, so two, two, three, and US is just one.
And that means that the phone number length is not consistent, right?
We can't just assume it's 10 digits and then take everything off the end because some countries use nine or eight. Okay.
So then I realized we're going to have to do all countries.
I found this scrape thing on the web, scraped it, brought it into Excel.
There were some weird things like dashes. I got rid of the dashes.
And then the thing that completely I was not expecting, but I'm glad I checked home, conditional formatting, highlight cells, duplicate values.
Can you believe this?
There are places that all have the same country code.
So Australia, Christmas Island, Cocos Island, New Zealand, and is that Pitcairn? Sorry, I don't know how to pronounce it.
Oh, shoot. US and Canada, Russia and Kazakhstan, and more.
All right? So a few of these.
I just cheated and said, all right, if you have 47, then it's either Norway or these.
If it's 61, it's probably Australia, but it might be Christmas Island or Cocos Island.
But US and Canada, I had to split those out.
So for me, I went and found the 50 country codes for Canada and said, if it starts with one followed by these three digits, then it's Canada.
And I probably should go do the same thing for Russia or Kazakhstan, but in this case, I didn't.
All right?
So I ended up with a really long list of every possible country code and the country that it belongs to.
And then I realized that because we don't know how many digits there's going to be from the left, we have to organize this by the number of digits in the country code.
So I took all the six-digit country codes and the country, and I named that.
Now, my idea was to name this CC6.
It turns out you can't name it CC6 because that's a cell darn it. So I called it SIX, S-I-X.
And then here, all the four digit country codes, I called Four, F-O-U-R.
And then here, all the three digit country codes, I called Three, and then Two.
And then Russia, United States, Kazakhstan are the one-digit codes.
All right? So I have those five named ranges.
Now, to get the country code, what we're going to do is pretty easy at first, we're just going to do a VLOOKUP of the first six digits after the plus sign into here.
All right?
If it happens to be 441481, we know it is Guernsey. We're done.
All right?
But the odds are it's not going to be one of these.
And so then if that returns an NA, so if it is NA, then we need to come and look at the first four digits after the plus sign and see if it's in this table. If it is, we're done.
Otherwise, the first three digits after the plus sign in this table, then the two digits after the plus sign in this table, and then the one digit after the plus sign in this table.
All right.
This formula's not bad, and I like this formula.
I know you could do this with LET, but it's just really simple to me and anyone who can do a VLOOKUP or index and match in Excel is going to understand this. All right?
So here's the VLOOKUP of the phone number, starting in position two, because of the plus sign. We want six digits.
That's going to return text of course, so I add zero to coerce it back into a number.
Look at our VLOOKUP table called SIX, get the second item, and zero for false for an exact match. All right.
But see, the IFNA has two components, the VLOOKUP, and if that VLOOKUP returns an NA, then we're going to move on and grab the four digits after the plus sign and look in the table called Four.
And if that's an NA, then we're going to move on and grab the three digits after the plus sign, and then the two digits, and then one.
And finally, if none of those work, then we're just going to admit defeat and call it an error.
Okay.
So simple little formula, but of course, you can't just take this formula and put it into your sheet because you don't have these five named ranges.
But here I think is what's going to be the really easy way to do this.
Down in the YouTube description, I'm going to put this link.
We're going to come out to Chrome and paste that link, and you should end up at this workbook.
And then what we can do is file, save as, download a copy.
And there we are. So there's our CountryCodeLookup.
Open that. Okay.
So now, here, you are on your computer.
You're going to enable editing, and what we want to do is get these five named ranges over to your workbook.
So right here, I have any list of phone numbers, and you have sheets in your workbook, whatever they are. We're going to come to CountryCodeLookups.
I'm going to right-click on Sheet One, move or copy, create a copy to any list of phone numbers, move to end, and that should successfully create a list of range names.
There they are. So those names range are all there.
So now, we should just be able to take this cell and copy it to your first phone number, double-click to copy that down.
And I think you're going to have a pretty reliable formula that will take these numbers and pull out the country for those.
Okay, wait, let's make it safer.
So Yitzie's numbers all started with a plus sign in this formula will work for that.
If you have phone numbers that don't start with a plus sign, they're either numbers or text, then this alternate formula here is better.
It starts at the first position instead of the second position.
And if it's a mix of both plus signs and not plus signs, then how to use the LET function.
Sorry, that's only going to work in newer versions of Excel to substitute the plus sign with nothing. All right?
So you got basically 1, 2, 3 different choices of what you're going to copy to your workbook.
Also, added instructions here on copy this worksheet to any workbook with a list of phone numbers and right-click, move or copy.
And I changed it from Sheet One to CountryCodeLists, so it won't run into that same name as yours.
All right.
Well, hey, I want to thank Daniel for tagging me in that question, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.

 

[jdellasala]

I LOVE this, and so took it to the next level. I created a Power Query table using the often overlooked Enter Data function where a table can be created by simply typing values into blank cells. No, I didn't type all 6 lists, I just copied them to Notepad which made them tab delimited values, and then pasted them into the Enter Data function. This is the PQ M Code for that:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVhbc9s4D/0rmj53d2zd9ZikuTWXzcZJdr7u7AMssTIbSXQpyV77138gCMqKtNOXZHRMESBwcADq778/heEyTJefPn+67oVuWnH49M9nQqMgRPSr0CMs9g1221bCU9+9B2joh6W/MPAFNFAAI348RYIZEvqInMMGamgdFBOk11CoAcumL0aLKRIHM8S4dNaUvayqAUsJ62TZgwdN4RlL/fBKal4517KT7cZ7k7qUjYdnxYXOlXTqSrCYnioIjXOvv69+/88tgnDqaRBGhBxqmK6NpmEN4miGJDMknSDhYmoTMzlDZm8FyxkytRUGs7dC89a50PUQ13CWrnDuYmJcvNZiDE1PH82CHS1na5bTFEXh1FiUzNak07NG6WzndHr6eEb7eDmNdBxMbcXhbB9i+Euv31ti5QXIXLUfuRATnx9U07VCa+gYTUxoH5XuNli8WJBa4r6TNxNb3VC7Z39qP515Tac/q4WWObJyBbXiX5LFlIHJYnrCxDcWV7Lp0CPQnWCZSMKp5SSaWk4ik60VmJfv+1w6mGj+RdWyQY8YS6aJTdIp05J0ZpKOZg28ySYX+N8EHSPoWf7JRrRurbH6hMLYKe8Zk2LhdDHNYEoxcO413rPY9utK5vzrjJOpP41i6v9yh/CXv840IKUCe9G4vICCzvei1lC6A8TZEIM72XWWdo9iJ/ngaTLNS5qY2vuKUj3EP1tMY5vNyJER/acR9JdEENV3G2+FOtEw6hPFtcpz5Snt/SXaztB6hS1CA68xjp1VJTLTITEVTyPbATGHv5frAz/7hhXXUK/dAstP0YgSKkbINPQo/tAMywICK8mPIdUR0sP9bk57u1P64F0oaDtGqYVhMUusxCto+ci+ydGjRMf5mTKkSvdzRrLZSI5FsBgckn3L2NKe63T2wKdCM4rg3QvVCIZJTDeYB34One3hRUrUBgp+JGHFUtBQeWff9ZxkPms/SoJSzkdbf1vhvQldONuWWgoJVwvi1RPSMJdb/p3a4+XPHjqF3lTeOKDUO65h7QyQXjg3zMRhivRCNS5o1Eu/iFrlqIi44ldLeRxQlbMVDdn87Vy2LfSMx6Mh4LYpUFC9P3KBf18wzBK9PvBCEipxyDeiqlgx7KwyojS1vuc9hoHNRuaIl91Gqq3LRURJVDUSzSF0rh9yjRXC5KNR4E40jtORcf8FmuOJrpHx/LUc2UosE3s8BSMpVdjR1MLPnjMSGx+/jcojXhL3CiihzYH5GtsKOaiuE6Y4nwWWnEsUjV/fZL2G9d7tSpyDWp62jWw5wZ69iY3D96JV3YazREJ2rrp2P3CXlGy1h6M0PY0x0lNVo1Rw3LPFIGg3ohLu5YyijVnTjmKZsXCm+7V7NttfgVbiQ9f0s8zOI6IZzAaUzGu5xiLpOCwB5fMJG3DvtCSgfN73/4oa86dLBk2EbrUY7UajdD5GItK29ZDSgFL6YMzxMxXhYatZEgJK6JUcO0lC0lcluGIPaEi4l92mP+1MQ8E9dLsBMF5ftp06LSH5U1Whdg6hItK1OK2JSLYqGDyiPnHWFEprtyaxkwuw+geJbfF2XmkcaPx+M2XMtRNQM39915hVwYjVbe0oFaQ+D0VGyzXvRNPMnWrVzgEkdlrh5u494+SqUrvTSVJLvRYBUq0boY+iVDvZuAUZ5SIXxRCikDrLxVHkm49qGdoOgwbgfVgbUBZwLXqLjY2VPlosiIHVu8nhBxJGCztLV/IoGPDtKNcJVAtgzOx7WWGXrHZ4a9KMmlTdqKboNbjdIuoCWNGA9x/GSPhV24Hpzg6kqQcrsHbAaSZ7Ms3G6vqDkZBKuWOY8NyAZMGKRhWJae7wHoSiYZ/x1tXxIjqgqqSjYZTZEx5c/UcZHS/vT0fLInKPTnFgKCHZRTZhg2LI+Pyq+9EiUg2MRw40XjwKMzVTuD1zKRxU3E7Vl9jRvRdZs1k7NuM60HnnQhVTjZyjwgrJgJU9FF1+ts5u8cL5KPbjbhdTpbxgi3LPdAhVoa59vAjGVDBv0GDi3bYZ1f0Ptkql8oSU4J+pUP6CqpJ2tLvqu54DGtuJWKn3jzaoah4lN4WYauY098dUMXdSyzV0zihNNHioC6hGRRGnNNn0O6icN+QsztZYJk+qOuB87dYSS17UuxhcJ0o8yFyr8TLbfHS7wTN98DuNhhuQd6eczKekwUj/EsGmZMyOczlbSiM7rKJKF9K9Rh0JuKukFNZzaMoKCtFuGKS0gdyzTmWxHdWqQu6YQBl10HuBSq7cIuPQV6WL4S3jzergRDqjfnmr4Sc/Gufu+j0WFAMkWdAX0jvTsB5eM0n4n6iF29YE/4/aWUmYGDhJOxXNSPpfG9mJgrbyLmuJ85PznXh+22oQFQPEcdgYIWbE+PonuCaY2S6w6bvBbGx1Gcct52hi2bLlLplRSl/gh3yX7fAeFbu5CJsWM4Kp7xxR+EH+GEAa4ITSpTOREUcPujwcR++SDhzXYmSHZPuyPGx5ZLeFZ24jdva1gr/gCSDn7rOkAwyaYUHfKnQpe3u9ptH7SkPj3qLb5taFjgbtmx5rnsdIGpdvO6j4GxvNizgLDi2GGsledkdr1oJ0CerbzrGHpltO6Z1sykJZb2jI/SKaGvS7BYhFe1EwX+hbBFbPHg6fvRWW7Bq0vS9+pf58cOtSGnNOHtB4JDQ6yupKc5Bg2aPyexD/ugufvea7oSuyY0SJ9w3XXakWz7WZ8uwz9aWNrFjNae4x0uiIT4PPG7b9I/YgVgmuQzgMurF0cTKz9WfvYqORBDW4byuIqOmXFipUnPvH8mO8f0Jn5HY7fByw8y2q3zcBdq8n2eUgtQ0Y1e8KUwFbVCULkXBs4DTIkkx/hS3TMvUHHo6kzNh5k6Jr+ANOyqHhyFkFxZLhr7Skn3RvsY8+CcC4yIg830u8Ho5AoqmW3j007/wmjevYiGtX5qlVKP6ac2LcqhvUw+TpuW8xcqbD3sER3jfWyD//Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Country = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}})
in
    ChangedType

That loaded into a table named CountryCodes and then used that table to lookup the country using 365 functions:

CountryCodeLookups.xlsx
	A	B
1	+447594361587	United Kingdom
2	+41769482539	Switzerland
3	+35056369249	Gibraltar
4	+972536992461	Israel
5	+12017961582	United States
6	447594361587	United Kingdom
7	41769482539	Switzerland
8	35056369249	Gibraltar
9	972536992461	Israel
10	12017961582	United States
11	33143122222	France
12	14166961000	Canada
13	13305551212	United States
14	2344603600	Nigeria
15	441624680680	Isle of Man
16	41416323030	Switzerland
17	5342350300	Cuba
18	+35056369249	Gibraltar
19	5342350300	Cuba
Sheet1

Cell Formulas
Range	Formula
B1:B19	B1	=LET(Ph,SUBSTITUTE(A1,"+",""), IFNA(XLOOKUP(LEFT(Ph,6)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,5)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,4)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,3)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,2)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,2)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,1)+0,CountryCodes[Code],CountryCodes[Country]), "#ERROR!"))))))))

One other thing I rarely see used, in the Queries & Connections list in the Worksheet, you can right click on the query and select Export Connection File...

This will save the query to your default My Data Sources folder which will be under the Documents folder which may be in your OneDrive folder if you use it. The file will be saved with "Query - " and the name of the Query with the extension .odc.
Once saved, it can be retrieved in any new Workbook using Data -> Existing Connections listed under Connection files on this computer. One warning - it will attempt to load as a Table in a new Worksheet (which does NOT get named properly) with Add to the Data Model checked.

I've posted the problem on the Excel Feedback Portal here. An vote would be greatly appreciated!

 

[MrExcel]

Wow JDellaSala - this reply is awesome on so many levels.

The binary encoding on the M code like a great way to pass secret messages to someone.

I voted for your idea on the Feedback Portal (actually, I was signed in as MrsExcel, so I guess she voted.

I've now successfully saved the Telephone Country Codes to my Existing Connections and can easily add it to any future workbook. There could be a lot of uses for this!

Thanks!
Bill