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).
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).
Any luck on getting TEXTJOIN to work?
Actually yes kind of 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
Wait this email is the reminder I’ll get back to you shortly… I need sleep haha
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!