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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,272
Office Version
  1. 365
Platform
  1. Windows
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.
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 

ayalaz

New Member
Joined
Nov 14, 2005
Messages
7
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:
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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.
 

ayalaz

New Member
Joined
Nov 14, 2005
Messages
7
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?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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:
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,625
Messages
5,832,748
Members
430,163
Latest member
YesImAk

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
Top