matching country codes

ayalaz

New Member
Joined
Nov 14, 2005
Messages
7
I have a list of calls made to many countries around the world and a list of the country codes ranging from 1-5 digits. I need a way to match the country code part of the telephone number to the country code list and use the rate next to this code for calculating the cost!

Number Dialed Country Code Rate
[011359604503867 35 3
[0113545600938] 994 4
[011994505637853] 995 3
[01148225171582] 996 7
[011302310795526] 48 4
[01159889376468] 482 6
[01148507026968] 30 2
[01148225171582] 7 5
[0113904378558] 7095 6
[01170957979786] 33 1
[011963112779779] 85 3
[01133475488927]
[01185223441330]
[01131317681040]
[01170957979785]
[01196899314110]
[01170957979785]
[011968702772]
[01179168533650]
[0112022663499]
[011963112779779]
[01170957979785]


[/img]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
ayalaz

Welcome to the Mr Excel board!

It is usually a good idea to show a small sample of your worksheet. To do so, see the link at the bottom of this page to Colo's HTML Maker. Also read the ‘Sticky’ titled: 'HTML Maker FAQ (How to show your sheet on the board)' which is at the top of this ‘Excel Questions’ forum. Test it out in the ‘Test Here’ forum which is found in the ‘MrExcel Message Board Forum Index’ before trying to use it in this forum.

I am sure your question will be answered as soon as we know just what we are dealing with. It sounds like you will have a table of countries, codes and costs and will use the VLOOKUP function. However, to be more precise I would want to know a bit more about your current data.
 
Upvote 0
Hi:

Just a few comments....in terms of the data you have displayed ...does the phone number always begins after '011', and if the country code can be 2, 3, 0r 4 digits, how is the distinction to be made on how many digits to evaluate for the country code. Can the data be converted to a format that can be more easily evaluated, such as 011-49-XXXXXXXX, 011-7035-XXXXXXX, etc?

plettieri
 
Upvote 0
The phone number always begin with 011
The country code can be 1-2-3-4-5 digits
and, unfortunatley the data cannot be converted... :cry:
 
Upvote 0
Hi:

If the country codes can be 1 - 5 digits, what infomation can be used to determine the number of digits in country code in the sequence of numbers supplied? It seems we have a variable that we can not define!!


Anybody else?

plettieri
 
Upvote 0
ayalaz said:
I have the full list of Country Codes
does this help?

If we strip off the 011 bit as well as whatever country code we have, are we dealing with a fixed number of left-over digits?

I suspect not, but if we are then it is simple.
 
Upvote 0
I can strip off 011 easy, but cannot strip off the country code!
the country code is variable length.

I could sort the country codes numericaly,
7
30
33
35
48
85
482
994
995
996
7095
does this help?
 
Upvote 0
I know the CC's are of varying lengths; but if you know the rest of the phone number is of a constant length, then you ALSO know the length of the country code. :biggrin:
 
Upvote 0
Hi:

From our international phone bills the telephone number beyond the country code varies...ie sometimes the country region is part of the number and the phone numbers in themselves vary in length depending on the country......maybe ayalaz's data is different than than what I have seen.

plettieri
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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