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.

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.

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.

Hi Sasa
You are quite right about the leading space, which if it is a problem can be
removed with Trim, as you say.

I am aware that there is no need to copy the data before parsing with text
to columns, but I had assumed that Christopher would want to keep his source
data intact, as there is other information there that he might need.


--
Regards
Roger Govier



"Sasa Stankovic" wrote in message
...
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.

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