1

I have a file with text divided into cells that I want to group into another cell. Cells are:

enter image description here

A, B, C, and D are what I have. In each coluimn, if the value is Yes, I would like the Result column to display the title of each Yes. So for the first one, as an example, BlueCOMMA GreenCOMMA YellowDOT Is this possible to achieve? If so, how? Thank you.

Edit: 2016 office without 365 subscription doesn't have textjoin. Looking for a LibreOffice / Googledocs answer.

2

You can use TEXTJOIN for this if you have Excel 2016 and a 365 subscriber.

In F2 use the below, and drag down:

{=TEXTJOIN(", ",,IF(A2:D2="yes",$A$1:$D$1,""))}

Enter this as an array using Ctrl+Shift+Enter

If you don't have 365 subscription, you can use a long winded IF statement, again enter in F2 and drag down:

=SUBSTITUTE(TRIM(IF(A2="yes",$A$1,"")&" "&IF(B2="yes",$B$1,"")&" "&IF(C2="yes",$C$1,"")&" "&IF(D2="yes",$D$1,""))," ",", ")

For Google Docs Use:

=ArrayFormula(TEXTJOIN(", ",TRUE,IF(A2:D2="Yes",A$1:D$1,))&".")
  • Your answer seems to be the way to go. I do have office 2016, however I don't have the text join function. Is there a way to add this? I found a post saying that only 365 subscribers get it... – riseagainst Jul 31 '18 at 13:39
  • It seems LibreOffice may have it, I'm gonna give it a shot. – riseagainst Jul 31 '18 at 13:45
  • I didn't realize TEXTJOIN was only available to 365 subscribers, that's pretty crappy of MS. I have an update for my answer you can use anyway @riseagainst – PeterH Jul 31 '18 at 13:48
  • That would also work, but my example is very simplified, I have some 40+ columns. I think libreoffice will work, I just can't get the syntax right. – riseagainst Jul 31 '18 at 14:25
  • Have you tested this on excel? I can't get it to work on libreoffice or google docs. – riseagainst Jul 31 '18 at 17:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.