Need to chck if a keyword is present in a text string and return keyword if yes

**Jekaterina** · 12-16-2012, 04:10 PM

Dear friends,

I've got a column with addresses as a text string. Text may contain name of a street, city, house number etc. Besides there is no strict order that name of street should come first, it can be anywhere in a text string. Another table contains two columns: first one with keywords (street name as a keyword) and second column contains relevant postal code. I need to check if any keywords from second table is present in each cell of column with addresses, if yes then return name of street (keyword) or postal code relevant to this keyword. I'll be very grateful for your help! Regards, JekaterinaExample.xls

**FDibbins** · 12-16-2012, 04:38 PM

Based on your sample file, try this, copied down...
=IF(ISNUMBER(SEARCH(E2,A2,1)),E2,IF(ISNUMBER(SEARCH(F2,A2,1)),F2,""))

edit: this searches for either the keyword (returns keyword) or postal code (returns postal code)

**Ghozi Alkatiri** · 12-16-2012, 04:40 PM

try it
=IF(ISNUMBER(FIND(E2;A2));E2;"No")

**Pete_UK** · 12-16-2012, 04:44 PM

Put this array* formula in B2:

=IF(MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6)))=0,"",INDEX(E:E,MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6))))) & IF(MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))=0,"",INDEX(F:F,MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))))

then copy down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

Hope this helps.

Pete

EDIT: Sorry, you won't be able to use the full-column references in the arry formula as you have XL2003 - try this one instead:

=IF(MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6)))=0,"",INDEX(E$1:E$6,MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6))))) & IF(MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))=0,"",INDEX(F$1:F$6,MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))))

Still committed with CSE.

File attached to demonstrate.

Pete

**FDibbins** · 12-16-2012, 04:44 PM

@ Ghozi, nice 1

note though that "find" looks for an exact match (upper and lower), while "search" ignores case. in the examples provided, your formula will work perfectly

but if the case of just 1 letter was changed, it would fail

**xladept** · 12-16-2012, 04:49 PM

Where would you like the Keyword or Code Returned - Or are you asking for a function??

**Jekaterina** · 12-16-2012, 05:25 PM

Thanks Pete! It is just brilliant!

**dredwolf** · 12-16-2012, 05:55 PM

You could try this one, although it does use a helper column:

Formula:

Please Login or Register  to view this content.

See attachment

Need to chck if a keyword is present in a text string and return keyword if yes

LinkBack

Thread Tools

Rate This Thread

Display

Need to chck if a keyword is present in a text string and return keyword if yes

Re: Need to chck if a keyword is present in a text string and return keyword if yes

Re: Need to chck if a keyword is present in a text string and return keyword if yes

Re: Need to chck if a keyword is present in a text string and return keyword if yes

Re: Need to chck if a keyword is present in a text string and return keyword if yes

Re: Need to chck if a keyword is present in a text string and return keyword if yes

Re: Need to chck if a keyword is present in a text string and return keyword if yes

Re: Need to chck if a keyword is present in a text string and return keyword if yes

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions