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:

Name	Day		Expected Result
Bob	W		Bob, Sue, Jane, Jill
Sue	WF
Tim
Jane	MWF
Jack	TR
Jill	MTWRF

<colgroup><col width="88" span="4" style="width: 66pt;"></colgroup><tbody>
</tbody>

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

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

<tbody>
</tbody>

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/