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]
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,521
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,306
Members
412,716
Latest member
thviid
Top