TextJoin! Let's make it happen! <3

[George_Copelan1]

The challenge:

Not being able to combine rows together with clean separation.

The solution:

The ability to use a formula like this =TEXTJOIN(" | ",TRUE,AQ2:BJ2)

This would be a HUGE help for me (and I’m sure many others).

[Cody_Light]

Any luck on getting TEXTJOIN to work?

[George_Copelan1]

Actually yes kind of :smiling_face_with_halo: it requires four columns but I made it happen. Want me to share? On my phone currently. Send me an email to remind me. George@laboremedge.com

[George_Copelan1]

Wait this email is the reminder :rofl: I’ll get back to you shortly… I need sleep haha

[George_Copelan1]

Column 1: "Join - Step 1"
Concatenate a bunch of columns together (replace column 1-10 and add as many as you want)

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE({Column 01},",",{Column 02},",",{Column 03},",",{Column 04},",",{Column 05},",",{Column 06},",",{Column 07},",",{Column 08},",",{Column 09},",",{Column 10}),",,,,,",","),",,,,",","),",,,",","),",,",","),",,",",")

Column 2: "Join - Step 2 (leading comma exists)"
Will show true if leading comma exists still in Step 1

IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Join - Step 1},",,,,,",","),",,,,",","),",,,",","),",,",","),1)=",","true",BLANK())

Column 3: "Join - Step 3 (leading comma removed)"
Self explanatory

IF({Join - Step 1 (leading comma exists)},SUBSTITUTE({Join - Step 1},",","",1),{Join - Step 1})

Column 4: Join - Step 4 (ending commas exist)
Same as Step 2 but ending comma

IF(RIGHT({Join - Step 3 (leading comma removed)},1)=",","true")

Column 5
Finishes it up.

SUBSTITUTE(IF({Join - Step 4 (ending commas exist)},REPLACE({Join - Step 3 (leading comma removed)},
LEN({Join - Step 3 (leading comma removed)}),1,""),{Join - Step 3 (leading comma removed)}),","," ➕")

Let me know if there is any issues or questions. Happy to walk you through it!

[Rob1]

TEXT join is needed BADLY especially since its very common for the primary field to be a formula.

The need to do so many nested ifs and substitution formulas is brutal.