Display city name by area code

[Firasath]

HI, I would like to display the name of the city in the A Column automatically by entering the telephone number in a cell in B Column. Here the first two numbers of the telephone number are area code. '04' & '44' is for Dubai, '06' & '73' is for Sharjah, '02' is for Abu Dhabi. Example is pasted below. Please advise.

<table border="0" cellpadding="0" cellspacing="0" width="179"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> </colgroup><tbody><tr style="mso-height-source:userset;height:20.1pt" height="26"> <td class="xl66" style="height:20.1pt;width:48pt" height="26" width="64">City</td> <td class="xl66" style="width:86pt" width="115">Account No.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl67">043962676</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">024457073</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">043962676</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">065752332</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">042977613</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">731721093</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">065459445</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">065454025</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">043257143</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">042614331</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">042208323</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">026457994</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl68" style="border-top:none">441144091</td> </tr> </tbody></table>

 

[JamesW]

Hi,

I would put all of your area codes + cities into another table and do a vlookup:

Also, I though +44 was UK?

=VLOOKUP(LEFT(B2,2),Sheet2!A1:B24,2,FALSE)

 

[syntaxed]

Best practice would be what James suggests, alternatively in cell A2:

=IF(OR(LEFT(B2,2)="44",LEFT(B2,2)="04"),"Dubai",IF(OR(LEFT(B2,2)="06",LEFT(B2,2)="73"),"Sharjah",IF(LEFT(B2,2)="02","Abu Dhabi")))

And drag down.

 

[Firasath]

Dear James,

The '44' is the starting number for Internet account .

It worked great. But if I would like to add 3 digits number code, then it would not work. In this case as the other gentlemen suggested to use 'if' function, that would I think would work. what do you suggest.

 

[JamesW]

Hmm,

syntaxed's solution works well, and you can specify the number of digits, but if you have hundreds of different variations then it will start to cause problems.

Another possible way would be like this:

City        AC#            Digits
            34111          2
            33332          3

Then use this:

=VLOOKUP(LEFT(B2,C2),Sheet2!A1:B24,2,FALSE)

 

[Firasath]

Dear James,

I can't understand how this formula is going to display the city name. Please elaborate little bit more.