this post was need for other post
How to prevent duplicated Text from (TEXTJOIN function)?
How to prevent duplicated Text from (TEXTJOIN function)?
join.xlsx | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | ||||||||||||||||||||||||
2 | TXT | AA | # AA | B | # B | A | # A | B | # B | C | # C | AA | # AA | G | # G | F | # F | # | # | |||||
3 | AA/B | |||||||||||||||||||||||
4 | A | |||||||||||||||||||||||
5 | B / C | |||||||||||||||||||||||
6 | AA | |||||||||||||||||||||||
7 | G | |||||||||||||||||||||||
8 | F | |||||||||||||||||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:V2 | C2 | =IF(MOD(COLUMN(C$2)-COLUMN($C$2)+1,2)=0," # ","")&IFERROR(MID(TEXTJOIN("/",TRUE,$A$3:$A$34),FIND("|",SUBSTITUTE("/"&TEXTJOIN("/",TRUE,$A$3:$A$34)&"/","/","|",CEILING((COLUMN()-COLUMN($C2)+1)/2,1))),FIND("/",TEXTJOIN("/",TRUE,$A$3:$A$34)&"/",FIND("|",SUBSTITUTE("/"&TEXTJOIN("/",TRUE,$A$3:$A$34)&"/","/","|",CEILING((COLUMN()-COLUMN($C2)+1)/2,1))))-FIND("|",SUBSTITUTE("/"&TEXTJOIN("/",TRUE,$A$3:$A$34)&"/","/","|",CEILING((COLUMN()-COLUMN($C2)+1)/2,1)))),"") |