Numbers - IF Contains Specific Text then Return Value

Poohlian wrote:

search if a cell in the "Description" column contains "7-Eleven" or "Citgo" and have it return the text "Gas" in the "Category Type" column on the same row.

I would also like to add more search functions to it, such as, if a cell in the "Description" column contains "Twitch" it will return the text "Subscription" in the "Category Type" column on the same row, and so on.

Welcome to the Community. An AppleScript can certainly do this. But it is probably easier to categorize using the IFS and COUNTMATCHES functions like this.

=IFS(COUNTMATCHES(C2,"CITGO"),"Gas",
COUNTMATCHES(C2,"7-ELEVEN"),"Gas",
COUNTMATCHES(C2,"TWITCH"),"Subscription",
TRUE,"")

What I do is use option-return to separate the formula into lines as shown in the screenshot. That makes it easy to add a new COUNTMATCHES line before the TRUE whenever I need to add a new search term and category pair. If you have a lot of search term/category pairs the formula will get long, but it will still work and be easy to edit.

SG

Here is a change to the plan so Table 2 can be sorted, you can add rows in the middle, and you can delete rows (just not the footer).

• Add a footer row to Table 2
• C1 has no formula.
• C2 =IF(ROW()=2,A2,OFFSET(C2,−1,0)&IF(A2≠"","|"&A2,""))
• Fill down to complete the column (except the footer row)
• My table 2 has 26 rows. The footer is row 26 and the formula in the footer cell C26 is =OFFSET(C$26,−1,0)
• Formula in Table 1::E2 is =IFERROR(XLOOKUP(REGEX.EXTRACT(LOWER(C2),LOWER(Table 2::C$26)),Table 2::A,Table 2::B),"")
• You can hide Table 2::C and the footer row when it is all set up.

The dumb thing I did earlier was a test to see how many characters can be in a cell. I went a little overboard and it never finished calculating. I went a little slower the next time and got it up to over 10 million characters so there is no way the concatenated string in Table 2 is going to exceed the limit.

The easiest way I can think of to do this is the following (easiest in terms of use but not easy formulas):

Table 1 is the table from your screenshot. Table 2 is a lookup table where you enter the company names in column A and the categories in column B.

Table 1::E2 =IFERROR(XLOOKUP(REGEX.EXTRACT(LOWER(C2),LOWER(Table 2::C$1)),Table 2::A,Table 2::B),"")

fill down to complete the column.

Table 2::C1 =XLOOKUP("*",C,C,"",2,−1)

Table 2::C2 =A2

Table 2::C3 =C2&IF(A3≠"","|"&A3,"")

fill down to complete the column

You can hide column C after everything is set up, which will make it look much prettier. And you can Cut/Paste Table 2 to a new sheet to get it out of the way.

The gist of it is this:

REGEX.EXTRACT can extract a specific word from a string. If it is told to extract "Citgo|7-Eleven" from a string it means to extract either "Citgo" or "7-Eleven" if it finds one of them in the string. It will return only one (or give an error if it finds none). It is case sensitive so I used LOWER to make the string and the search case insensitive. The formula then uses XLOOKUP to look in Table 2::A for the found word and return the category from Table 2::B. Table 2::C concatenates each of the companies from Table 2::A to make the string required for REGEX.EXTRACT. The formula in Table 2::C1 looks up the last entry in column C.

There may be special characters that could mess up the string for REGEX.EXTRACT. Try to avoid special characters in the company names.

I'm not sure that will be possible in Numbers, at least not in a practical manner. Maybe in Excel with an "array" formula. Maybe. If the description was simply "Citgo" or "7-Eleven" it would be simple to do with a lookup function but not when the word is part of a larger piece of text. Lots of things are possible with AppleScript but it takes someone to write it.

I'm not sure that what you are asking will suit your needs. For example, you might go to 7-Eleven for beer and munchies and not get gas, which means "gas" would be the wrong category. Or you might go the the grocery store to get cough medicine, not food, so "food" would be the wrong category. You might be better off having the category column be a column of pop-up menus with your list of categories to choose from, like in the Personal Budget template.

In case it gets buried in the thread, I recommend trying the IFS with COUNTMATCHES approach posted above.

It's easy to implement. No extra tables or complicated things like regex to worry about.

Also it's easy to maintain. To expand you just add a new search term/desired return value pair in the middle of the formula before the TRUE.

SG

Hi Poohlian,

For help with Numbers view the guide found in the article here: Add calculations to category groups in Numbers on Mac and Create a custom cell format in Numbers on Mac

You can also find a full guide for Numbers, Shortcuts, and Automator below. We provide these guides as they might be helpful for you to use to be able to get the results you are seeking with the Numbers app.

Numbers User Guide for Mac - Apple Support

Shortcuts User Guide for Mac - Apple Support

Automator User Guide for Mac - Apple Support

If you need further assistance, reach out to the Numbers experts via Apple Support.

Contact - Official Apple Support

Contact Apple for support and service - Apple Support

Best.

I was just thinking that Table 2 is not sortable. If that is an issue, a sort-safe formula can probably be made for column C. I'd work on it right now but I did something really dumb and Numbers is stuck "calculating" (hopefully not culminating in a crash).