# How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question.

Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the articles and books I'm reading. Say I'm reading "I, Claudius". I want to give it these tags: history, fiction, biography, disability, politics, drama. Then, if I want to display all the articles/books tagged with "politics", I can maybe search/display/pivot with that tag.

Maybe preferably, the tags should all be in one cell with each word separated by a comma. If each word were in a cell of its own, it would make the table really messy, I think.

I am open to other labelling solutions too.

Thanks!

-
I've never seen a tag solution in excel, but that's not saying there isn't one. Maybe check out evernote – Raystafarian Mar 27 '12 at 12:59

As far as I know there are no built-in features that can parse and summarize comma-separated tags in Excel. You can, of course, create your own solution with worksheet functions and a little VBA. Here's a quick solution for doing this.

Step 1: Press Alt+F11 to open the VBA editor pane in Excel. Insert a new module and paste in this code for a custom function.

``````Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr
out = ""
i = 1

Do While i <= UBound(rra, 1)
If rra(i, 1) <> False Then
out = out & rra(i, 1) & sep
End If
i = i + 1
Loop
out = Left(out, Len(out) - Len(sep))
CCARRAY = out
Exit Function

EH:
rra = rr.Value
Resume Next

End Function
``````

This function will allow you to create comma-separated lists to summarize the tag data you have.

Step 2: In a worksheet, enter in a cell (H2 in the example below) the tag you want to search for. In the cell to the right, enter the following formula by pressing Ctrl+Shift+Enter.

``````=IFERROR(CCARRAY(IF(NOT(ISERROR(FIND(H2,\$B\$2:\$B\$6))),\$A\$2:\$A\$6),", "),"No matches found.")
``````

By pressing Ctrl+Shift+Enter, you are entering the formula as an array formula. It will appear surrounded by `{...}` in the formula bar. Note that in the formula `\$B\$2:\$B\$6` is the range that holds all the tags for the items listed in `\$A\$2:\$A\$6`.

EDIT:
If you don't mind your matches being listed in a column instead of in a list in one cell, you can return matches for tags using only worksheet functions.

Where your titles are in `Column A`, the tags are in `Column B`, and the tag you are searching for is in `H2`, you can use the following array formula in `I2` and fill down as far as you need:

``````=IFERROR(INDEX(\$A\$1:\$A\$6,SMALL(IF(NOT(ISERROR(FIND(\$H\$2,\$B\$1:\$B\$6))),ROW(\$B\$1:\$B\$6),2000000),ROW()-1)),"")
``````

The formula works by first forming an array of numbers based on whether the tags in each row contains the search term. If a match is found, the row number is stored in the array. If it is not found, 2000000 is stored in the array. Next, the `SMALL(<array>,ROW()-1)` part of the formula returns the `ROW()-1`th smallest value from the array. Next, this value is passed as an index argument to the `INDEX()` function, where the value at that index in the array of titles is returned. If a number greater than the number of rows in the title array is passed to `INDEX()` as an argument, an error is returned. Since 2000000 is passed as the argument when no matches are found, an error is returned. The `IFERROR()` function then returns `""` in this case.

It is important to grasp how `ROW()` is being used in this formula. If you want to display your list of results starting in a different row, you will need to adjust the second argument of the `SMALL()` function so that it returns the first smallest value from the array. E.g., if your list of results starts in Row 1 instead of Row 2, you would use `SMALL(...,ROW())` instead of `SMALL(...,ROW()-1)`.

Also, if your list of titles and tags does not start in Row 1, you will need to adjust the formula as well. The second argument of the `IF()` function must be adjusted so that a match in the first row of your data returns 1. E.g., if your list of titles starts in Row 2 instead of Row 1, you will need the formula to include `IF(...,ROW(\$A\$2:\$A\$7)-1,...)` instead of `IF(...,ROW(\$A\$1:\$A\$6),...)`.

-
Wow, this works perfectly! I prefer it on Google Spreadsheet, though. But it uses JavaScript. I'll try and do something similar there though I am a complete and utter beginner with the language. Just two more questions: What does it mean to "summarize" data? What does your code in step 1 do? I'm asking this question in particular because I think I might have to do the same with JavaScript in Google Spreadsheet. Thanks so much for your solution! – nusantara Mar 29 '12 at 16:07
Great, I'm glad it's of use. By "summarize", I just mean creating a comma-separated list of matching titles. The VBA is required for this purpose. With built-in Excel functions, it's fairly straightforward to create an array formula that can pick out matching tags. However, it's not possible to return these results in one cell without VBA, because the `CONCATENATE` function in Excel cannot take an array as an argument (it only reads the first item from the array if you try it). The VBA function is made to create comma-separated lists from an array of values. – Excellll Mar 29 '12 at 16:38
After writing the above response, I realized that there is a way to do something similar without VBA. Instead of returning the list of matching titles in one cell, a column of matching titles can be returned. I can edit my post to include this method as well if you're interested. – Excellll Mar 29 '12 at 16:40
It's possible without VBA? That would be better, I think. If it's not too inconvenient, yes, it would be awesome if you could include the method you mentioned. It would be so helpful to anyone searching for this topic! Thanks for explaining "summarization". I tried concatenation too but, yeah, couldn't use it the way I wanted it to. Now I know it can't behave like an argument. – nusantara Mar 31 '12 at 10:04
Yes, it's possible. I'll try to post the solution here in a few minutes. Sorry for the delay! – Excellll Apr 5 '12 at 13:57

It's not clear to me exactly how you want to use the tags. But in Google Sheets, you can use the SPLIT() function to split a string of tags (delimited by spaces, commas, or whatever you want) into several other cells, and I'm sure even Excel has a function that can search a cell for a tag.

-

Another idea:

Use the inbuilt filter functionality. Filter by the Tags column, then you can search for rows containing a given tag.

A row with the tag list: comedy,horror,romance

would show up when searching for any of those three tags.

-