Check if string contains keyword from a list and identifies the word matched

**PaulG442** · 09-27-2012, 11:51 PM

I refer to the solved string: "Check if string contains keyword from a list, regardless of case or positioning in string" and this is follow on question.

In the above, an excellent and useful formula was provided that matched a search string of text against a number of key words and identifed with TRUE or FALSE as to whether one of those key words was present in that search string.
The formula is: =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$5,A2)))>0 and was provided by TIGERAVATAR.

What I should like to be able to do is to identify which of the key words were in fact identified (or the first thereof).

My model has multiple text strings that contain informaiton I need to summarise. to give an example:

"Supply and install pavement arrows including glass beads and angular quartz Straight ahead and left or right combined in Preformed Thermoplastic"

The key words i need to identify in the above (and summarise on) are: "Thermoplastic" AND "arrow".

If anyone could assist I would be most appreciative.

Many thanks.

**Jockney** · 09-28-2012, 08:59 AM

If string is in A1 then in B2 put

=IF(AND(COUNTIF(A1,"*Thermoplastic*")>0,COUNTIF(A1,"*arrow*")>0),TRUE,FALSE)

This will return true or false.

If you wanted to have a cell reference rather than the words "Thermoplastic" AND "arrow" then change to "*"&C1&"*"

Hope this helps

**Jockney** · 09-28-2012, 09:00 AM

Opps amature mistake

You can forget using IF and just use the AND formulae

=AND(COUNTIF(A1,"*Thermoplastic*")>0,COUNTIF(A1,"*arrow*")>0)

**tigeravatar** · 09-28-2012, 11:12 AM

@Jockney, I don't think OP wants TRUE/FALSE as the formula result, he wants the actual words that were found. So in the example, the formula result should be something like "Thermoplastic, Arrow".

@PaulG442, Welcome to the forum!
Unfortunately, Excel's native CONCATENATE() function doesn't work with arrays. This means that what you're looking for can only be accomplished if you don't mind doing one of three options:

Use separate cells for each identified keyword found in the string (So "Thermoplastic" would be in one cell of results, and "Arrow" would be in the next cell of results).
Have a very, very long formula to get all results in a single cell (Because it would have to build the string by checking each keyword individually, which is not very effective if your keyword list ever grows/shrinks).
Use VBA for a macro or UDF (User Defined Function) that will concatenate arrays.

Let us know which option you'd like to pursue. Also...
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.