What function to use in excel?

[Legacy 199112]

I have a range of the first five digits of telephone numbers and I need to replace them into country name. so i need to compare every single digit of these telephone numbers with the range of prefixes i have before deciding the country name. is there any function to do that ?

I used lookup function but it compare the whole number exactly
example if i have the number 27800 and in my prefixes i only have 27 it will not recognize it. and it wont specify it as south africa phone number.

please specify the format of the function to use ?

 

[Marcelo Branco]

for USA all the numbers started with 1 except some numbers like 1602 it is for Canada

So the entire number - country-code concatenated with phone number - for US has less 3 digits than a entire number for Canada? Am i right?

 

[Legacy 199112]

no,

numbers for USA and Canada are equally length but however I am taking only the first five digits of each number ( that is country code and the only few digits of the rest of the phone number)

for USA lets take an example if the five digits was not like 1506 then its USA if it was 1506 it is for Canada.

 

[Marcelo Branco]

Sorry, i'm not following you.

Let me try again
1. All the numbers in your list have 5 numbers (for US, Canada or whatever)

2. Your table country-code x country-name has 1 digit for some country-codes and 2 digits for others

How can i know if a number like 12345 is for US = 1 and Phone=2345 or SomeCountry=12 Phone = 345?

 

[Legacy 199112]

cause there is no country code with 12
and the country code vary from one digit to five digits.

12 and 13 and 14 it is all inside US it is like for tell in what state the phone number is.

 

[konew1]

I think you need to rebuild your table of valid country codes and the country names.
Insert 5 columns between the code and the name. Use this to populate the first new column =IF(LEN($A1)=1,$A1,""), change "=1" to "=2" for the next column, etc. That will give lists of codes for each valid length. Then for your lookup use this ugly formula
=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(A9,F1:G4,2,FALSE),VLOOKUP(A9,E1:G4,3,FALSE)),VLOOKUP(A9,D1:G4,4,FALSE)),VLOOKUP(A9,C1:G4,5,FALSE)),VLOOKUP(A9,B1:G4,6,FALSE))
I'm sure someone here can improve on that!, but it seems to work for me.