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]

Hi and welcome

Maybe something like

=VLOOKUP(1*LEFT(A2,2),C2:D200,2,0)

where
A2 = 27800

C2:D200 is the location of the range of prefixes and assuming the prefixes (column C in this example) formatted as numbers and column D contains the country names

M.

 

[Legacy 199112]

Hi Marcelo,
thank you but the thing is my prefixes doesn't have the same length..see for south africa the country code 27 but for example canada 1306 can you help me in that

can the lookup compare digit by digit so if there is two prefixes one 98 and 20 and i have the phone number 20200 i need it to take the 20 and replace it with Egypt. not with Iran (98) .

Thank you again.

 

[taurean]

It will be easier to devise a formula if you could change the format
From:
20200

To:
20-200

This way there will be some identifiable pattern to search the country code.

 

[Marcelo Branco]

Hi Marcelo,
thank you but the thing is my prefixes doesn't have the same length..see for south africa the country code 27 but for example canada 1306 can you help me in that

can the lookup compare digit by digit so if there is two prefixes one 98 and 20 and i have the phone number 20200 i need it to take the 20 and replace it with Egypt. not with Iran (98) .

Thank you again.

Could you post a sample of your prefix/country name table showing the different situations?

To do this
Select a relevant part of your table including headers
Put borders
Copy (Ctrl+C) and Paste(Ctrl+V) in the forum reply page

 

[Legacy 199112]

see this is the prefix table example

<table border="0" cellpadding="0" cellspacing="0" width="271"><colgroup><col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:7533;width:155pt" width="206"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:49pt" height="20" width="65">full_code</td> <td style="width:155pt" width="206">country_name</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">1</td> <td>USA</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">7</td> <td>Russia</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">20</td> <td>Egypt</td> </tr> </tbody></table>
and this the table range of phone number

<table style="width: 251px; height: 40px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:5522;width:113pt" width="151"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:75pt" align="right" height="20" width="100">20200</td> <td style="width:113pt" width="151">

</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">12095</td> <td>
</td> </tr> </tbody></table>

 

[Marcelo Branco]

The phone numbers have the same numbers of digits to US/Russia, for example, that have only one digit as contry-code and for Egypt? Or one number less for US/Russia?

 

[Legacy 199112]

no, the phone number ranges for all the countries are equally length.
the differences are in the prefix.

 

[Marcelo Branco]

no, the phone number ranges for all the countries are equally length.
the differences are in the prefix.

So are you saying that these numbers, for example,
12xxxxxx
12xxxxxx

can be either
US = 1
Phone number 2xxxxxx

or
some country = 12
Phone number
xxxxxx
?

If so, how can we know the country-code?

 

[Legacy 199112]

no, let me explain with another example ok for egypt

we have the prefix 2010 , 2011 and 2012 are for Egypt mobile
and for 20 are egypt land
as well there is 98 for iran

when i have the phone number 202010
after the lookup i have iran not egypt land


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