Return text found in a search
 

I have column with data like:

A1: 123 Main St. San Francisco
A2: 5654 Moon Hollow Way Oakland

I have over 10 thousand of these. My goal is to pull the city out, but since
the city is in different places (and after x y or z number of spaces, etc.)
I cannot seem to do it with a simple text formula.

and I have another list of cities only like:

C1: San Bernadino
C2: Oakland
C3: San Francisco

My question is -- can I do a formula, say in B1, that would search through
my list in C1:C2 within A1, and return the found match?

In other words the result would be:

A1: 123 Main St. San Francisco
B1: San Francisco

(e.g. it found that "San Francisco" was contained within A1, and returned
the match from the list of cities in C1:C3)

Any ideas?

Name your city list as a named range say citylst (using Insert, name
define..)

then use this formula on cell B1 and copy all the way down.
=INDEX(citylst,MATCH(1,COUNTIF(A1,"*"&citylst&"*") ,0))
Array entered(ctrl+shift+enter)

It will pull the name of that city that is in the cell



"Dave R." wrote in message
...
I have column with data like:

A1: 123 Main St. San Francisco
A2: 5654 Moon Hollow Way Oakland

I have over 10 thousand of these. My goal is to pull the city out, but
since
the city is in different places (and after x y or z number of spaces,
etc.)
I cannot seem to do it with a simple text formula.

and I have another list of cities only like:

C1: San Bernadino
C2: Oakland
C3: San Francisco

My question is -- can I do a formula, say in B1, that would search through
my list in C1:C2 within A1, and return the found match?

In other words the result would be:

A1: 123 Main St. San Francisco
B1: San Francisco

(e.g. it found that "San Francisco" was contained within A1, and returned
the match from the list of cities in C1:C3)

Any ideas?

Try...

B1, copied down:

=INDEX($C$1:$C$3,MATCH(TRUE,ISNUMBER(SEARCH($C$1:$ C$3,A1)),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Dave R." wrote:

I have column with data like:

A1: 123 Main St. San Francisco
A2: 5654 Moon Hollow Way Oakland

I have over 10 thousand of these. My goal is to pull the city out, but since
the city is in different places (and after x y or z number of spaces, etc.)
I cannot seem to do it with a simple text formula.

and I have another list of cities only like:

C1: San Bernadino
C2: Oakland
C3: San Francisco

My question is -- can I do a formula, say in B1, that would search through
my list in C1:C2 within A1, and return the found match?

In other words the result would be:

A1: 123 Main St. San Francisco
B1: San Francisco

(e.g. it found that "San Francisco" was contained within A1, and returned
the match from the list of cities in C1:C3)

Any ideas?

Thanks Domenic and Norman! They both work great.


"Domenic" wrote in message
...
Try...

B1, copied down:

=INDEX($C$1:$C$3,MATCH(TRUE,ISNUMBER(SEARCH($C$1:$ C$3,A1)),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Dave R." wrote:

I have column with data like:

A1: 123 Main St. San Francisco
A2: 5654 Moon Hollow Way Oakland

I have over 10 thousand of these. My goal is to pull the city out, but
since
the city is in different places (and after x y or z number of spaces,
etc.)
I cannot seem to do it with a simple text formula.

and I have another list of cities only like:

C1: San Bernadino
C2: Oakland
C3: San Francisco

My question is -- can I do a formula, say in B1, that would search
through
my list in C1:C2 within A1, and return the found match?

In other words the result would be:

A1: 123 Main St. San Francisco
B1: San Francisco

(e.g. it found that "San Francisco" was contained within A1, and
returned
the match from the list of cities in C1:C3)

Any ideas?

Hmm, I'm not sure why I think or thought your name is Norman, I haven't been
here in awhile. I think I was thinking of Norman Harker.

Thanks anyway :)


"N Harkawat" wrote in message
...
Name your city list as a named range say citylst (using Insert, name
define..)

then use this formula on cell B1 and copy all the way down.
=INDEX(citylst,MATCH(1,COUNTIF(A1,"*"&citylst&"*") ,0))
Array entered(ctrl+shift+enter)

It will pull the name of that city that is in the cell



"Dave R." wrote in message
...
I have column with data like:

A1: 123 Main St. San Francisco
A2: 5654 Moon Hollow Way Oakland

I have over 10 thousand of these. My goal is to pull the city out, but
since
the city is in different places (and after x y or z number of spaces,
etc.)
I cannot seem to do it with a simple text formula.

and I have another list of cities only like:

C1: San Bernadino
C2: Oakland
C3: San Francisco

My question is -- can I do a formula, say in B1, that would search
through
my list in C1:C2 within A1, and return the found match?

In other words the result would be:

A1: 123 Main St. San Francisco
B1: San Francisco

(e.g. it found that "San Francisco" was contained within A1, and
returned
the match from the list of cities in C1:C3)

Any ideas?