On Thu, 27 Sep 2007 00:09:00 -0700, Christopher Naveen
wrote:

Hi,

I have 1 column which contains address and city name now i want a formula
which shuold look for city name and shud return the same city in next col.
This i can do using filters (which contains = XXX and i can put the values in
the next col) but pls tell me how to do automatically using the formula.

Would appreciate the quick response.

Thanks in advance.

Example :

# 49, MG road, Chennai now I shud look for the string "Chennai" and shud
put the value "Chennai" in B1
81, II cross, viveknagar, Hosur
123, ALS Nagar, Chennai
555, Kormangla, Chennai


-Christ.


Assuming your City name always follows the last comma in the string, then the
following should do this:

=TRIM(MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1,",",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(
A1,",",""))))+1,255))


--ron