VLookup A Partial Value or Few Characters From Cell

[msiyab]

Hi Guys,

First of all, sorry for naming the thread as "Need Help!!" as i do not know what to name my problem exactly.


Ok, now basically I would like to know if there is a formula or something for the following:

We have 10-digit invoice numbers that belong to different cities. For example, 3301023689 belongs to city X and 3302011438 belongs to city Y. Basically, the 33"01" or "02" determines the city. So i would like to know is there any formula for getting the city name automatically on the next cell if the invoice number has 3301, 3302, 3303 and so on. [Invoice number in A1 and city code in B1]

I would like the formula to be according to the following details. If invoice number contains:
3301 - DXB
3302 - AUH
3303 - Al Ain
3304 - SHJ
3305 - RAK
3307 - AJM ------ There is no 3306
3308 - FUJ

Also, is there any way to split the invoice number, ie, 3302011438 into 33 02 011438 or inv. no. 3301000012 into 33 01 000012


Thank you in advance.

[Admin]

Hi

for the first query, try

=VLOOKUP(LEFT(E1,4),INDEX(TEXT($A$1:$B$7,"@"),0,0),2,0)

and for the second one

=TEXT(E4,"## ## ######")

where E1 and E4 holds the invoice numbers, A1:B7 house the invoice code and the city name.

[msiyab]

Thanks for your help.
As i was struggling with this code, my colleague helped me with another formula:


=IF(C30<=3302000000,"DXB",IF(C30<3303000000,"AUH",IF(C30<3304000000,"Al Ain",IF(C30<3305000000,"SHJ",IF(C30<3307000000,"RAK",IF(C30<3308000000,"AJM",IF(C30<3309000000,"FUJ","Others")))))))

and it works fine too. Thanks for the help on splittin the data too.