0

I’m trying to list and count all tags in my table. For some reason, anything after comma, is getting a white space before the word. So if the tag is found on the beginning of a record, it is seen as different tag. example: “Service” and “ Service” are seen as 2 separate values, when they should be one, and not have that white space.

I’ve fiddled with trim, text join, etc. But I always seem to end up with this problem.

—————————————
record1|tag1, tag2, tag3
record2|tag1, tag2, tag3
record3|tag3

Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1Fg914iNeG6i8TLSim3YFH-GSql9gPgs7eL8F5RZQ_OU/edit?usp=sharing

Here is the formula I was playing with:

The List:

=unique(transpose(split(CONCATENATE(arrayformula(B2:B9&", ")),", ", TRUE, TRUE)))

The count:

=ARRAYFORMULA(if(countif(transpose(split(CONCATENATE((B2:B9&", ")),", ")),F2:F) = 0, "",countif(transpose(split(CONCATENATE((B2:B9&", ")),", ")),F2:F)))
0

lostnconfused, try this in G2:

=ARRAYFORMULA(COUNTIF(B$2:B$9,"*"&TRIM(SUBSTITUTE(F2:F5,CHAR(160),""))&"*"))

You've got non-printing ASCII character 160 masquerading as a space (ASCII 32) in there, which seems to be where your problem is coming in.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy