What function to use in excel?

L

Legacy 199112

Guest
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 ?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
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.
 
L

Legacy 199112

Guest
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

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414

ADVERTISEMENT

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
 
L

Legacy 199112

Guest
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414

ADVERTISEMENT

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?
 
L

Legacy 199112

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
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?
 
L

Legacy 199112

Guest
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

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