Assign a category to an identified keyword in a line of text

Except when I have just a very small handful of keywords I've never had much luck in Numbers doing this kind of thing with formulas. It can be done but it's cumbersome. In most working situations I find a short script is far more efficient. For example, something like this:

I set up the strings in a "Strings" table with a blank column to the right of them.

I set up the keywords and categories in a separate "Categories" table.

I make sure I don't have blank rows in the tables.

Then I run the script, click B2 in the Strings table and command-v or Edit > Paste and Match Style.

The script is below.

Copy-paste into Script Editor (in Applications > Utilities folder), make sure Numbers and Script Editor are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility, and click the 'Run' button. Then click the top cell where you want the results and paste.

Note: To keep the script simple I've avoided blank rows in the tables. It could be modified to accommodate blank rows if necessary.

SG

tell application "Numbers" to tell front document's active sheet
	set theStrings to value of cells 2 thru -1 of column "A" of table "Strings"
	set theKeywords to value of cells 2 thru -1 of column "A" of table "Categories"
	set theCategories to value of cells 2 thru -1 of column "B" of table "Categories"
end tell

set text item delimiters to ", "
set outStr to ""
repeat with aString in theStrings
	set thisLine to {}
	repeat with i from 1 to (count of theKeywords)
		if aString contains item i of theKeywords then
			copy item i of theCategories to end of thisLine
		end if
	end repeat
	set outStr to outStr & (thisLine as text) & return
end repeat
set the clipboard to text 1 thru -2 of outStr

Here is a working solution that can easily be expanded to include any number of keywords, and will allow for up to 2 keywords, and thus two categories, for any entry.

The formula in cell B2 is copied across to E2 and down, thus it can easily be expanded to check for any number of keywords. Well, up to 250 or so keywords, I should say, as 255 is the column limit in numbers. In this example, even the column heading in B1 was filled across. Adding more keywords really is as simple as adding rows to Table 1, and an equal number of columns to Table 2.

https://www.icloud.com/numbers/0cnNLvzAiu_fx-DImTx5rKtxQ#Blank

You can check the formulas used for yourself.

The columns in grey are for calculation only and would be hidden in your finished sheet. You could adapt this to allow for three, or even more keywords to exist in the same line, if you wanted to, though for simplicity I limited this example to two.

As Quinn pointed out, a separate column is needed for each keyword check, unless you intend to nest functions within other functions, as I originally proposed, which can quickly become outrageously complex. As Numbers does not permit array functions, there is no way around this. These columns can of course be hidden. They could also exist in a separate table, though it is generally better to keep your calculations close. Forcing Numbers to go looking for things on other tables and other sheets, as with Excel, will slow the whole thing down.

OK, we’re halfway there. The above answer covers how to create a column with the keyword placed in it. Column C in my example is your column E. Now we just need to match that keyword to a category. For this, you will just use a 2 column lookup table, with keywords in one column and categories in another. Then use LOOKUP, VLOOKUP, or my personal favourite and recommendation, INDEX:MATCH to look up the keyword in the lookup table and result the corresponding category.

It appears that what you have in columns E:F is exactly what we need. That is your lookup table. One of the great features of Numbers is the ability to have multiple tables in a single sheet. I recommend splitting that off into a separate table.

Assuming these are the only four keywords you have and you do not intend to create more, it can be done the way I described (using SUBSTITUTE) without too much trouble. Let me know if you still need help and I can help form the exact functions you’ll need.

The above solution uses SEARCH rather than SUBSTITUTE as we are only checking one keyword per column and thus it is simpler.

[Optional] Calculation column headings. Can be filled across.

Calculation column keyword checks. Can be filled across. Cell will contain the number associated with the keyword in your keywords table (Table 1) if the check is passed (keyword present). Otherwise it will be blank.

Category 1. Matches the lowest number (MIN) in the check columns with its associated category. If expanding this to allow for more than two keywords in a single line, use LARGE or SMALL in place of MIN and MAX.

Second category. If only one keyword is present, this column is blank. Otherwise, it looks up the category associated with the largest (MAX) value from the check columns.

This is, I believe, as simple a workaround as you’re going to get.

Hi ohi3000,

So you have noticed that Numbers is not good with arrays.

I can see a solution where there would be an extra column for each of the possible keywords terms. Yikes! (I assume you have more than these keywords).

The difficulty for Numbers occurs when we need to search for "sitting" in the "Go working" and other cells. And we could worry about what happens if you are sitting and eating a banana.

quinn

After all that I see I still have not answered the question. You wanted all the categories listed in one cell, didn’t you?

That’s possible too, it just requires a few additional steps. You can see everything I did here:

https://www.icloud.com/numbers/0cnNLvzAiu_fx-DImTx5rKtxQ#Blank

Numbers does lack certain functionality present in Excel, such as array functions. But there is always a workaround if you work at it.

You’re probably wondering at this point why Table 2 could not simply list the categories in the order we want - why the need for Table 3? The short answer is those zeros we needed to create for LARGE to work properly. I’m sure there is a way though to render Table 3 obsolete, but I’ll leave that up to somebody else to find. I’m done.

Upon consideration, I came up with a much simpler solution than nesting IF functions within other IF functions. You’ll still need to perform multiple SUBSTITUTION checks though. Make sure that the number of characters removed from the string when the substitution is performed is different for each keyword. So, for example, you might replace “sitting” with “sittin”, “banana” with “bana”, work with “w”, etc. Adjust the lookup table to contain numbers instead of keywords. 1 would be sitting, 2 banana, 3 work, etc.

Now, instead of using =IF(LEN(B1)+7=LEN(A1),”Muffins”,””) as suggested above, you’re instead going to simply look up the value LEN(B1)-LEN(A1) in the lookup table and return the corresponding category.

Alternatively, if using a large number of keywords, you may want to consider adding characters in your substitution rather than removing them. You could also do away with the need for a lookup table entirely by instead using CHOOSE, though I would not recommend this option for a large number of keywords. A lookup table is far more elegant than a long CHOOSE function.

I’m not very good though at keeping it simple, so here’s a solution that will allow up to 999 keywords to exist on the same line. Columns B:E are almost the same as above. The only difference is the result if false is changed from a null string to 0 to allow LARGE to work without a cell warning.

I simply changed the last argument from “” to 0.

Here is the new header for Category 1 (F1):

This is filled to the right to create the headers for the remaining columns in the sheet (Categories 2-4).

Here is the formula to determine Category 1 for the first entry (F2):

You will notice that this is considerably more complicated than my original formula in this cell. However, unlike the original, this formula can be filled across, so there is just the one formula, not two as in the original.

I’ve created here only enough columns to go to 4 keywords, but these formulas, without making any changes whatsoever, are designed to work up to 999 separate keywords in a single line. Just add more columns.

Who needs array functions? ;)

Oops, that wasn’t quite right. Last revision, I promise.

Category column headers contained an absolute value, and adding additional keyword check columns would break that formula. This is correct.

When adding new columns, you would need to manually adjust the blue reference, which appears three times, to the new range. This too could be automated with ADDRESS and INDIRECT, but I don’t see any value in complicating this further.

In the end, I found a way to greatly simplify the whole process, making not just Table 3 obsolete, but Table 4 as well, doing it all in Table 2 as you wanted, with half as many columns. Ignore the above. Use this method instead.

This is filled down for all of column B.

This is filled across for all other keyword check columns: one per keyword. These columns can be hidden in your final result. Columns C:H differ from B only in that a comma and a space are added at the beginning of the string result if they are not the first keyword check that is passed.

One column only for Categories, and it’s a simple CONCATENATE.

I loved SG’s AppleScript solution. Not having access to that ability on iOS forces some pretty time consuming workarounds, as you can see - though I did make it harder on myself than it needed to be, by failing to see the simpler solution until the end, as you can see if you follow my work. Nevertheless, I had fun doing it. Glad we could help.

Removed

It’s a bit difficult to visualize what it is you are trying to do, but if I’m interpreting correctly, if a cell in column A contains a particular word, you want a particular result in column B?

This can be achieved with SUBSTITUTE. Suppose the keyword you are looking for is “muffins”. You could use:

B1=SUBSTITUTE(A1, “Muffins”, “”)

Hide column B, as it is a calculation column, and in column C, use:

C1=IF(LEN(B1)+7=LEN(A1),”Muffins”,””)

B1 will contain the same text as A1, but the word Muffins, if it appears, will be replaced by a null string. Thus the overall string will be 7 characters shorter. If B1 is 7 characters shorter than A1, C1 will contain “Muffins”. Otherwise it will be empty.

If there are multiple keywords to search for, then you’ll need to perform multiple substitutions and then embed IF functions within other IF functions. Depending on how long your list of keywords is, it could get real complex real quickly. But this will work for a small number of keywords.

EDIT: Alternatively, you could use SEARCH in place of SUBSTITUTE in column B. If the keyword is present, the result will be a numerical value. If not, it will result in an error. In column C, you could then use:

C1=IF(ISERROR(B1),”Muffins”,””)

This method is simpler, but less versatile for multiple keywords.