Textjoin in an array using a search string

[mackensteff]

Hello,

I have being trying to use the new text join function in a formula with the criteria being to find a search string, and then joining cells that meet that criteria. In short the following is what I am hoping to accomplish when searching for "W" in the second column:

[TABLE="width: 352"]
<colgroup><col width="88" span="4" style="width: 66pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 88"]Name[/TD]
[TD="class: xl63, width: 88"]Day[/TD]
[TD="class: xl63, width: 88"][/TD]
[TD="class: xl63, width: 88"]Expected Result[/TD]
[/TR]
[TR]
[TD="class: xl63"]Bob[/TD]
[TD="class: xl63"]W[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Bob, Sue, Jane, Jill[/TD]
[/TR]
[TR]
[TD="class: xl63"]Sue[/TD]
[TD="class: xl63"]WF[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Tim[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Jane[/TD]
[TD="class: xl63"]MWF[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Jack[/TD]
[TD="class: xl63"]TR[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Jill[/TD]
[TD="class: xl63"]MTWRF[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

The issue seems to be using search, or find or countif in an array formula to identify the cells for textjoin.

Anyone want to give it a go?

Thanks

 

[Domenic]

The syntax for TEXTJOIN is as follows...

[TABLE="width: 600"]
<tbody>[TR]
[TD="width: 211"]TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)[/TD]
[/TR]
</tbody>[/TABLE]

Therefore, assuming that Columns A and B contain the data, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("W",B2:B7)),A2:A7,""))

Hope this helps!

 

[mackensteff]

This works great. Thank you!!!

 

[majidsiddique]

Hi Domenic,
i am using excel 2010. there is no EXCEL formula TEXTJOIN in it.

 

[mackensteff]

It can be introduced as a User Defined Function, but is only available as a function if you have an Office 365 subscription or Excel 2019.
https://www.pk-anexcelexpert.com/text-join-formula-for-all-excel-versions/