# matching country codes

#### ayalaz

##### New Member
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.

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

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...

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

I have the full list of Country Codes
does this help?

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.

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?

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.

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

1,214,262
Messages
6,118,550
Members
448,835
Latest member
Profast123

### 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.

### Which adblocker are you using?

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

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