Extract multiple keywords from text string

[HughT]

I have searched the forum but I can't find anything that quite matches this.

This is for a healthy eating project. Students record their meals in a daily diary. This is random unformatted text in a single cell (B1, B2 etc). There is a keyword list of healthy foods, each item being in a separate cell. What I would like to do is search the random text for occurrences of the keywords and return the keywords in another cell adjacent to the text cell. I would then like to be able to search the returned cells by the keyword list.

So:

Keywords (each in a separate cell, but doesn't have to be in Column A):

A1 Apple
A2 Fries
A3 Salad
A4 Burger
etc

Text (in B1)
Today I ate a burger with fries, and had an apple afterwards.

Result (in C1)
Apple Fries Burger [order is not important]

C1 to C20 (etc) will be the searchable data. I want to be able to search this by each keyword in the range A1:A4, ie 'Apple', 'Fries', 'Salad' etc so I can see who has been eating Apples, Fries, etc. Using column filters will display the contents of every cell, so if some comedian enters the whole range A1:A4 (which will actually be much larger) the filter will also return the whole range, so I need an alternative method.

I possible I would like to do this by a formula rather than VBA as I have to hand this over to someone who will not understand VBA, and can add to or alter the contents of the lookup range (A1:A4) simply by adding to it or overtyping the existing contents.

Thank you for your help.

 

[HughT]

Thank you for your most elegant solution.

For searching column C by individual keyword I was wondering if an Index / Match or Indirect function might work, but how?


Many thanks

 

[Peter_SSs]

For searching column C by individual keyword I was wondering if an Index / Match or Indirect function might work, but how?

I'm not really sure what you are after. Taking the sample data in post #10, and assuming the row values are concatenated in column C ...

What result (& where) would you expect if the word of interest was "apple"?

 

[HughT]

Results will be concatenated in column C, and from the example above, the first result will be Apple, Fries, Burger. Each cell in column C will contain a range of results from the available keywords derived from the freetext cell (potentially all of them).

What I want a user to be able to do is filter the rows by a single keyword, so for example by selecting 'Apple' they would see all the rows where 'Apple' appears in column C, even if the cell also contains other keywords such as , 'Apple Fries Burger' from the example above. It is possible to do this using Filters / Text Filters / Contains but this requires knowledge of how to do this and is not very user friendly, and is dependent on a user inputting the exact text string to obtain a match.

I was wondering if it was possible to use the keywords range as a lookup table to drive some sort of Index/Match or Indirect function, but at that point my Excel-fu gave up!

Many thanks for your help with this.

 

[Peter_SSs]

Results will be concatenated in column C, and from the example above, the first result will be Apple, Fries, Burger. Each cell in column C will contain a range of results from the available keywords derived from the freetext cell (potentially all of them).

What I want a user to be able to do is filter the rows by a single keyword, so for example by selecting 'Apple' they would see all the rows where 'Apple' appears in column C, even if the cell also contains other keywords such as , 'Apple Fries Burger' from the example above. It is possible to do this using Filters / Text Filters / Contains but this requires knowledge of how to do this and is not very user friendly, and is dependent on a user inputting the exact text string to obtain a match.

I was wondering if it was possible to use the keywords range as a lookup table to drive some sort of Index/Match or Indirect function, but at that point my Excel-fu gave up!

Many thanks for your help with this.

If the users are incapable of using AutoFilter and you insist on not having any vba then there will be no Filter as such. What would be possible is to produce another table somewhere using formulas that extracted just the rows of interest.

However, to indicate what word they are interested in they would have to type it somewhere or choose it from a data validation list drop-down. Typing it would still be dependent on the user "inputting the exact text string", something I think you doubt. If the user can't handle choosing from an AutoFilter drop-down then I'm not sure they could handle choosing from a Data validation drop-down. I'm not sure where that leaves you.


In any case, when you say "...they would see all the rows where...", do you just mean the column B values or do you mean all 20+ columns of your sheet?

 

[HughT]

Peter

Thanks for working with this.

Ideally I would like users to be able to select from a drop down using the keywords range, in a similar way to Data Validation. This means that they could only select what was available, and it would be simple to do.

If this would require vba then so be it, and I could write instructions as to how to amend the vba (eg if they needed to extend beyond 20 items) and then save the project. Unfortunately I am not vba literate so it could not go much beyond that.

However extracting the table with selected rows to somewhere else also sounds very interesting as it means that they would have a report on what they had selected too.

Many thanks!

 

[Peter_SSs]

If this would require vba then so be it,

Hmm, but if we use vba for this, we could also use it to produce the list in column C?


What about this?

In any case, when you say "...they would see all the rows where...", do you just mean the column B values or do you mean all 20+ columns of your sheet?

 

[HughT]

The organisation who I work for is generally anti anything vba because it means that when someone moves on and a change has to be made, there is nobody there who understands vba to do it and the whole system falls apart. We have a lot of legacy spreadsheets which people depend on which aren't supported. What we have built up to now works beautifully without vba so someone like me could easily amend the formulas to extend the ranges etc.

However if what we need could only be achieved by vba that is ok because if the worst came to the worst the availability of Filters / Text Filters / Contains is still there, just not as simple or quick to use for someone with little or no Excel understanding.

Thank you for being so helpful with this.

Regarding viewing the columns, this isn't an issue because it is simply a matter of hiding them and protecting the worksheet so that users can only enter data in the data entry cells and perform the search / filter function against specific keywords.

 

[István Hirsch]

In that case, you can use them (headers) for a much simpler formula in your columns.

Note that you still have the issue of the apple showing up when pineapple is in the text & apple is not, but you seem happy to accept that. (same issue could occur with "ham" and "hamburger" etc)

Formula in D2 is copied across and down.

Keywords

	B	C	D	E	F	G
		Apple	Fries	Salad	Burger
Today I ate a burger with fries, and had an apple afterwards.		Apple	Fries		Burger
Today I had eggs
apples		Apple
Salad, pineapple & ham			Salad
I ate 4 apples		Apple

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #99cc00"]Apple[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(SEARCH(D$1,$B2&D$1)<len<span style=" color:008000; ">($B2),D$1,"")</len<span> <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

It seems pineapple – apple and hamburger – burger problem can be solved by a little modification:

=IF(SEARCH(" "&D$1," "&$B2&" "&D$1)<len($b2),d$1,"")

 

[Aladin Akyurek]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Apple[/TD]
[TD]Today I ate a burger with fries, and had an apple afterwards.[/TD]
[TD]Apple, Fries, Burger[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Fries[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Salad[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Burger[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In C1 control+shift+enter, not just enter:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH($A$1:$A$4,B1)),", "&$A$1:$A$4,""),""),1,2,"")<strike></strike>

<strike></strike>For this formula to work, you need to add the <acronym title="visual basic for applications">VBA</acronym> code for ACONCAT to your workbook as a module, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

One should adjust the formula to the input, not dismiss out of hand..

{=REPLACE(aconcat(IF(ISNUMBER(SEARCH(" "&$A$1:$A$4&" "," "&B1&" ")),", "&$A$1:$A$4,""),""),1,2,"")}

Note that ACONCAT is a generic function, therefore preferable.just for that reason.

 

[Peter_SSs]

It seems pineapple – apple and hamburger – burger problem can be solved by a little modification:

=IF(SEARCH(" "&D$1," "&$B2&" "&D$1)<len($b2),d$1,"")

István
That stops "apple" being reported in "pineapple" but not "ham" in "hamburger"