That is nice solution but Christophe rshould AFTER DataText to columns
operation use TRIM function on columns with city names because they will
have space in front of city name.
so, in column C function should be: =trim(b1).
And, there is no need for copiing data from A to B. Text to columns works if
you select column A and use it.

Hope this helps

"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
Hi Christopher

One way. assuming you have a blank sheet with the data in column A.
Copy column A and paste to Column B.
Select column B.DataText to ColumnsdelimitedDelimiter CommaNextselect
first columnchoose do not Importrepeat for other columns other than
CityFinish

--
Regards
Roger Govier



"Christopher Naveen" wrote
in message ...
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.