Convert to 2019

[Dr. Demento]

I have 365 at the office but 2019 at home. Can someone help me "translate" this function so that it will work in 2019?

It's used to merge multi-select answers that are identified by starting with "# ".

TEXTJOIN("\",TRUE,FILTER([[#Headers],[A]:[G]],ISNUMBER(SEARCH("# *",[@[A]:[G]]))*("# *"<>""),"No results"))

Thanks, y'all.

 

[vladimiratanasiu]

It works in Excel 2019, too.

TEXTJOIN function - Microsoft Support

The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

support.microsoft.com

 

[Dr. Demento]

It's the FILTER aspect that doesn't work in non-365 products

 

[FormR]

("# *"<>"")

Hi, I don't understand what this is supposed to be doing, but for the rest, you could try:

=IF(COUNTIFS(Table1[@[A]:[G]],"# *"),TEXTJOIN("\",TRUE,IF(LEFT(Table1[@[A]:[G]],2)="# ",Table1[[#Headers],[A]:[G]],"")),"No Results")

Which might need to be committed with CTRL+SHIFT+ENTER

 

[Dr. Demento]

Thank you, FormR! That worked perfectly!!

 

[Dr. Demento]

Now that I think about it, the reason for the code that was confusing is because I had this monster formula that depending on the question type (multiple choice, true/false, multi-select), there was a different formula for how to extract the answer. Instead, I went a VBA route and thereby avoided the need for the formula to discriminate the question type.