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)))

migrated from superuser.com Oct 7 '18 at 19:29

This question came from our site for computer enthusiasts and power users.

  • 1
    Please read the guidance for the google-spreadsheets tag. – Twisty Impersonator Oct 7 '18 at 18:23
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.

Your Answer

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