Match a text

[Christopher Naveen]

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.

 

[Roger Govier]

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.Data>Text to Columns>delimited>Delimiter Comma>Next>select
first column>choose do not Import>repeat for other columns other than
City>Finish

 

[Sasa Stankovic]

That is nice solution but Christophe rshould AFTER Data>Text 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]

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.

 

[Ron Rosenfeld]

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