Search code examples
google-sheets-formulatext-classificationcategorization

How to use Google Sheets to categorise data, depending on text values in a column

I ask this, as I previously performed this task using Python. I'm moving on to a new position and I need to pass this task over to someone who does not know how to program.

I am hoping there is a simple and suitable Formula to use on Google Sheets.

in the following table on Google Sheets:

enter image description here

Is there a formula I could use to automatically categorise that data?

ie in pseudocode

If Column A contains "password" or "sign in":

Column B = "Account Access"

Else If

Column A contains "support" or "contact":

Column B = "Customer Support"

Etc for multiple other categories.

Solution

  • You can use REGEXMATCH:

    =IFS(REGEXMATCH(A1, "password|sign in"),"Account Access",
     REGEXMATCH(A1, "support|customer"),"Customer Support",
     TRUE,"other")

    Expand as needed.