Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup returns #NA when search value (text) has leading zeros. | Excel Worksheet Functions | |||
How to insert carriage return in the middle of a text formula to . | Excel Discussion (Misc queries) | |||
How do I search for text within text? | Excel Discussion (Misc queries) | |||
How can I make Excel return the text in column A only if there is. | Excel Worksheet Functions | |||
search multiple worksheets for an item and return the Wsheets name | Excel Worksheet Functions |