Create Empty Elements in a TEXTJOIN function

[rkaczano]

Is it possible to add empty elements in a TEXTJOIN function?

I have text strings in cells A1:A5

TEXTJOIN(",",, A1,A2,A3,A4,A5) creates the following text string:

Text1, Text2, Text3, Text4,Text5

I want the ability to introduce empty strings in between each element. And I want the user to have the option to make this variable in nature. So say I wanted 2 empty elements as follows:

Text1, EMTPY, EMTPY, Text2, EMTPY, EMTPY, Text3, EMTPY, EMTPY, Text4, EMTPY, EMTPY, Text5 EMTPY, EMTPY,

I don't want create the spaces in the range that feeds the function (i.e. I want to use range A1:A5 and not insert rows with empty data). I want to introduce spaces after-the-fact.

Note this does not have to say EMPTY it could also be spaces:

Text1, , , Text2, , , Text3, , , Text4, , , Text5 , ,

Thanks

 

[Special-K99]

Ignore this reply, have just understood what you're after.
Thinking...

 

[Rick Rothstein]

Is it possible to add empty elements in a TEXTJOIN function?

I have text strings in cells A1:A5

TEXTJOIN(",",, A1,A2,A3,A4,A5) creates the following text string:

You do not have to list each cell separately when they are contiguous like that, just use A1:A5 in place of all those single cell references. As for your question... put FALSE in as the second argument (it defaults to TRUE when you omit it)...

=TEXTJOIN(",",FALSE,A1:A5)

 

[Eric W]

Perhaps:

Book4
	A	B
1	Text1	Text1, Empty, Empty, Text1, Empty, Empty, Text1, Empty, Empty, Text1, Empty, Empty, Text1, Empty, Empty
2	Text2
3	Text3
4	Text4
5	Text5
Sheet2

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(", ",0,IF(MOD(ROW(INDIRECT("1:15"))-1,3)=0,INDEX(A1:A5,ROW(INDIRECT("3:17"))/3),"Empty"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Specific details would depend on your specific question, and what version of Excel you're using. Excel 2019 or Excel 365. Please update your user profile or let us know.

 

[Eric W]

Weird, I just noticed that it repeats Text1 5 times, even though the Evaluate Formula tool shows the different values in the last step. Try this:

=SUBSTITUTE(CONCAT(A1:A5&", Empty, Empty, "),", ","",15)

Edit: Versions of Excel before 365 did not handle INDEX in array formulas, so that's why the first formula did not work.