VLookup A Partial Value or Few Characters From Cell

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.

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.

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

Code:

---

=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.