Google-sheets – How to organize and add multiple tags to a list

google sheetsmicrosoft excel

I'm trying to create a sort of "goal bank" and I'd like to organize it so that you can search for goals based on various categories (i.e. diagnoses [autism, mental health, etc.], domains [social physical], etc.). I'd like to find a way to add tags or something to the goals listed, either in an Excel or Google Sheets (or some other platform if there's a better one), so that I could search one of the categories to find all the goals with that tag. Each goal would have multiple tags.

Here's kind of an example of what the sheet could maybe look like based on other responses that I've seen to similar types of questions. https://docs.google.com/spreadsheets/d/1rb2NBL5L-iOmRkJIVb431TnBKZf-k5PGWFcUtXpGXzY/edit#gid=0

Best Answer

It's not an easy task to do in a spreadsheet.

But one simple solution could be to make a new column that makes the tags filterable, using an expression like

 =CONCATENATE("|", REGEXREPLACE(B2; " *, *", "|"), "|")

which you then copy all the way down.

Then you get this (please excuse the semicolons in the formula; it's due to my Danish Google profile):

enter image description here

Then enable filtering by selecting Data | Filter from the menu.

If you then want to filter for tag b, you can filter the column "Filterable tags:" for |b|, i.e. with a vertical bar before and after the tag you want to filter for. You use "Filter by condition" and then "Text contains".

In this way, tags can even contain spaces, and you will always match the whole tag. (But of course it cannot contain commas, as you chose the comma as delimiter, and not vertical bars either, as I chose the vertical bar as delimiter in the "Filterable tags:" column. But those delimiters can be changed in the expression.)

In column B you can simply type the tags as you suggested, with spaces before and after the commas as you wish.

Here I filtered for |a| (sorry for the Danish text in the image; it's again due to my Danish Google profile, but I assume you can guess from the positions what the English text is):

enter image description here

Then I got this result:

enter image description here

It's not perfect, as every time you want to filter by tag you have to remember to add the vertical bars before and after. But it's at least one possible solution.

Note that searching for tags will also be possible by simply searching (Ctrl+F) for |tag|.

EDIT: Another expression might make the result prettier:

=IF(B2<>"", CONCATENATE("[", REGEXREPLACE(B2, " *, *", "] ["), "]"), "")

This results in

enter image description here

And then you filter or search for tags using simply [tag].

Related Topic