Textjoin

[Deelof]

Looking to use textjoin with multiple if statements to return the result in below example.
I need to identify if Width is populated to return "W 1400" or if Diameter is to return "DIA 1400"
Can anyone assist?

TEXTJOIN OUTCOME	NAME	#1	HEIGHT (mm)	WIDTH (mm)	DEPTH (mm)	DIAMETER (mm)	SEAT HEIGHT (mm)	ARM HEIGHT (mm)
HANLEY / TEAL / H 550 W 1400 D 420 SH 50 AH 100	HANLEY	BLUE	550	1400	420		50	100
HANLEY / TEAL / H 550 DIA 1400 SH 70	HANLEY	BLUE	550			1400	70

 

[Scott Huish]

I assume TEAL should be Blue, but don't you need to check for other things like depth and arm height existing as well?

 

[Scott Huish]

I would just use the CONCATENATE operator:
=B2&" / "&C2&" / "&IF(D2," H " & D2,"")&IF(E2," W " & E2,"")&IF(F2," D "&F2,"")&IF(G2," DIA " & G2,"")&IF(H2," SH "&H2,"")& IF(I2," AH "&I2,"")

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	TEXTJOIN OUTCOME	NAME	#1	HEIGHT (mm)	WIDTH (mm)	DEPTH (mm)	DIAMETER (mm)	SEAT HEIGHT (mm)	ARM HEIGHT (mm)		RESULT
2	HANLEY / TEAL / H 550 W 1400 D 420 SH 50 AH 100	HANLEY	BLUE	550	1400	420		50	100		HANLEY / BLUE / H 550 W 1400 D 420 SH 50 AH 100
3	HANLEY / TEAL / H 550 DIA 1400 SH 70	HANLEY	BLUE	550			1400	70			HANLEY / BLUE / H 550 DIA 1400 SH 70
Sheet1

Cell Formulas
Range	Formula
K2:K3	K2	=B2&" / "&C2&" / "&IF(D2," H " & D2,"")&IF(E2," W " & E2,"")&IF(F2," D "&F2,"")&IF(G2," DIA " & G2,"")&IF(H2," SH "&H2,"")& IF(I2," AH "&I2,"")

 

[Deelof]

I would just use the CONCATENATE operator:
=B2&" / "&C2&" / "&IF(D2," H " & D2,"")&IF(E2," W " & E2,"")&IF(F2," D "&F2,"")&IF(G2," DIA " & G2,"")&IF(H2," SH "&H2,"")& IF(I2," AH "&I2,"")

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	TEXTJOIN OUTCOME	NAME	#1	HEIGHT (mm)	WIDTH (mm)	DEPTH (mm)	DIAMETER (mm)	SEAT HEIGHT (mm)	ARM HEIGHT (mm)		RESULT
2	HANLEY / TEAL / H 550 W 1400 D 420 SH 50 AH 100	HANLEY	BLUE	550	1400	420		50	100		HANLEY / BLUE / H 550 W 1400 D 420 SH 50 AH 100
3	HANLEY / TEAL / H 550 DIA 1400 SH 70	HANLEY	BLUE	550			1400	70			HANLEY / BLUE / H 550 DIA 1400 SH 70
Sheet1

Cell Formulas
Range	Formula
K2:K3	K2	=B2&" / "&C2&" / "&IF(D2," H " & D2,"")&IF(E2," W " & E2,"")&IF(F2," D "&F2,"")&IF(G2," DIA " & G2,"")&IF(H2," SH "&H2,"")& IF(I2," AH "&I2,"")

Thank you and yes you are correct TEAL should be BLUE.
What if I want other fields adding in with " / " as a separator, this is why I started with textjoin. See below expanded example:-

TEXTJOIN OUTCOME

NAME

#1

#2

#3

#4

#5

#6

HEIGHT (mm)

WIDTH (mm)

DEPTH (mm)

DIAMETER (mm)

SEAT HEIGHT (mm)

ARM HEIGHT (mm)

HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / BLACK METAL HANDLE / 2 DOOR / LARGE / H 550 W 1400 D 420 SH 50 AH 100

HANLEY

BLUE

WOOD EFFECT

BLACK METAL LEGS

BLACK METAL HANDLE

2 DOOR

LARGE

550​

1400​

420​

50​

100​

HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / ROUND / H 550 DIA 1400 SH 70

HANLEY

BLUE

WOOD EFFECT

BLACK METAL LEGS

ROUND

550​

1400​

70​

 

[Scott Huish]

The reason I didn't use TEXTJOIN is because you want added text dependent on whether the cell has a value or not.

 

[Scott Huish]

But if you want to do it for the first part, do it for the first part.

=TEXTJOIN(" / ",TRUE,A2:H2)&IF(I2," H " & I2,"")&IF(J2," W " & J2,"")&IF(K2," D "&K2,"")&IF(L2," DIA " & L2,"")&IF(M2," SH "&M2,"")& IF(N2," AH "&N2,"")

 

[Deelof]

Thanks

But if you want to do it for the first part, do it for the first part.

=TEXTJOIN(" / ",TRUE,A2:H2)&IF(I2," H " & I2,"")&IF(J2," W " & J2,"")&IF(K2," D "&K2,"")&IF(L2," DIA " & L2,"")&IF(M2," SH "&M2,"")& IF(N2," AH "&N2,"")

Thanks, that is working now however if any of the cells aren't populated then the result contains a lot of spaces, is there anyway to remove these?

 

[Peter_SSs]

See if this does what you want.

23 11 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	TEXTJOIN OUTCOME	NAME	#1	#2	#3	#4	#5	#6	HEIGHT (mm)	WIDTH (mm)	DEPTH (mm)	DIAMETER (mm)	SEAT HEIGHT (mm)	ARM HEIGHT (mm)
2	HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / BLACK METAL HANDLE / 2 DOOR / LARGE / H 550 W 1400 D 420 SH 50 AH 100	HANLEY	BLUE	WOOD EFFECT	BLACK METAL LEGS	BLACK METAL HANDLE	2 DOOR	LARGE	550	1400	420		50	100
3	HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / ROUND / H 550 DIA 1400 SH 70	HANLEY	BLUE	WOOD EFFECT	BLACK METAL LEGS			ROUND	550			1400	70
TJ1 (2)

Cell Formulas
Range	Formula
A2:A3	A2	=TEXTJOIN(" / ",,B2:H2)&" / "&TEXTJOIN(" ",1,IF(I2:N2="","",LEFT(I$1:N$1,1+2*(LEFT(I$1:N$1,3)="DIA"))&SUBSTITUTE(MID(I$1:N$1,FIND(" ",I$1:N$1)+1,1),"(","")&" "&I2:N2))

 

[Peter_SSs]

See if this does what you want.

If it does, then would it be feasible for you to put the abbreviations required for columns I:N above the existing headings like this? It would make the formulas considerably less complex.

23 11 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1									H	W	D	DIA	SH	AH
2	TEXTJOIN OUTCOME	NAME	#1	#2	#3	#4	#5	#6	HEIGHT (mm)	WIDTH (mm)	DEPTH (mm)	DIAMETER (mm)	SEAT HEIGHT (mm)	ARM HEIGHT (mm)
3	HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / BLACK METAL HANDLE / 2 DOOR / LARGE / H 550 W 1400 D 420 SH 50 AH 100	HANLEY	BLUE	WOOD EFFECT	BLACK METAL LEGS	BLACK METAL HANDLE	2 DOOR	LARGE	550	1400	420		50	100
4	HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / ROUND / H 550 DIA 1400 SH 70	HANLEY	BLUE	WOOD EFFECT	BLACK METAL LEGS			ROUND	550			1400	70
TJ1 (3)

Cell Formulas
Range	Formula
A3:A4	A3	=TEXTJOIN(" / ",,B3:H3)&" / "&TEXTJOIN(" ",1,IF(I3:N3="","",I$1:N$1&" "&I3:N3))

 

[Deelof]

If it does, then would it be feasible for you to put the abbreviations required for columns I:N above the existing headings like this? It would make the formulas considerably less complex.

23 11 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1									H	W	D	DIA	SH	AH
2	TEXTJOIN OUTCOME	NAME	#1	#2	#3	#4	#5	#6	HEIGHT (mm)	WIDTH (mm)	DEPTH (mm)	DIAMETER (mm)	SEAT HEIGHT (mm)	ARM HEIGHT (mm)
3	HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / BLACK METAL HANDLE / 2 DOOR / LARGE / H 550 W 1400 D 420 SH 50 AH 100	HANLEY	BLUE	WOOD EFFECT	BLACK METAL LEGS	BLACK METAL HANDLE	2 DOOR	LARGE	550	1400	420		50	100
4	HANLEY / BLUE / WOOD EFFECT / BLACK METAL LEGS / ROUND / H 550 DIA 1400 SH 70	HANLEY	BLUE	WOOD EFFECT	BLACK METAL LEGS			ROUND	550			1400	70
TJ1 (3)

Cell Formulas
Range	Formula
A3:A4	A3	=TEXTJOIN(" / ",,B3:H3)&" / "&TEXTJOIN(" ",1,IF(I3:N3="","",I$1:N$1&" "&I3:N3))

This has worked a dream! Thank you.