Country Code Lookup

Bajjee

New Member
Joined
Feb 23, 2009
Messages
4
Hi,

We get a very detailed Call Data Records(CDR) for all telephone calls made by our employees. CDR typically contains the called number, duration in seconds and cost. What is missing it the city name and country name for the called number.

For e.g. a typical CDR log would be
Called # duration(secs) Cost
442075000000 40 0.02

From looking at this number, the country code is UK - 44 and the city code is 20 which is London.

I have a spreadsheet that contains Country code and city code for all major cities in the world.

Is it possible to create a lookup function, that would take the 'Called #' from the CDR and lookup the data in the CountryCity code spreadsheet and give me the called City and Contry name.

Please note the contry code is not always first two digits, it could more than two digits and same is with the city code.

Thanks for your help,
-Bajjee
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
based on your last sentence, you'd need to determine what part of the number is the actual phone number, strip it off (in the formula) and use what is left to determine the length of the string. I suppose, given a very limited knowledge of this, you could then check the length of the string and determine that if, for instance, the string is >4, then the country code must be the first 3 characters as opposed to the first 2.

After than, you'd use a vlookup() function to return the names of the country & city

Problem for me is, I don't know if all local phone numbers are 7 digits long. Here in the USA they are. Based on your post, it appears your local numbers are 8 digits long.
 
Last edited:
Upvote 0
Thanks for the quick reply. The problem is that I have no way to know the actual phone number. For e.g. in the same log, we have calls made to US phone numbers. In that case the CDR would contain

12068821645 where 1 is US country code and 206 is the city code. So we cannot apply a single formula to strip the actual phone number.

To give more details on the CountryCity code spreadsheet, it contains data as follows.

Country code, City code , Country Name, City name,
44, 20, UK, London
27,11, SA, Johannesburg
1,206, US, Seattle

etc.

So I believe the formula would be to concatenate Country and City code and do a VLookup with TRUE as the last parameter. But for some reason it is not working.

Thanks,
-Bajjee
 
Upvote 0
Look through the manual for your phone system that gives you that report, or talk to their support desk. You need to adjust that report so there is some sort of delimitation between the country code, city code and number. Even a blank space is enough to get the job done for Excel to parse that and give you a sure answer.

442075000000
...becomes
44 20 75000000
....or
44,20,75000000
...or even a more standard
44-20-75000000

Without some sort of delimiting character, Excel would be hard pressed to pick out codes of varying lengths. My understanding is that even the phone numbers themselve are different lengths, so that would mean ALL THREE sets of data would be different lengths from row to row.
 
Upvote 0
Another solution would be to take all you currently known phone numbers and develop your own database, without delimitors so that it looks at the whole number. In a 2nd field you have your City/Country. then you could use a vlookup.
 
Upvote 0
jbeaucaire - i checked with the vendor and it is not possible. :(

nbrcrunch - unfortunately we cant do that. We dont know what phone numbers users will be dialing.

Is there any string match function or so that i can use.

-Bajjee
 
Upvote 0
One the off chance that saying this will inspire someone to prove me wrong, I do not think you will find a solution.

There's is no way *I* can conceive of parsing out 3 randomly sized numeric strings out of a single numeric string...not with no clear basis for determining the size of each partial string item by item.
 
Upvote 0
Look at your Excel help for Text functions. That will get you started in understanding how to manipulate strings.

I still think this can be done. it is just considerably more time consuming that i care to spend my free time on.
 
Upvote 0
Thanks nbrcrunch. I have that for all cities in the world in a huge spreadsheet, one row for each city. It is against this spreadsheet, i want to do a lookup.

I tried couple of options as you mentioned and none of them work. I think, if I import them in database then I can make it work. But I want to just stick to spreadsheets for now.

Thanks for your patience,
-Bajjee
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

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