how to concatenate a string but with some ifs?

[jamiguel77]

hi all

i have this data:

A1	B1	C1	D1	E1	F1	G1
1​	3	29

I WANT CONCATENATE:

A3=Concatenate(A1,", ",B1,", ",C1,", ",D1,", ",E1,", ",F1,", ",G1)

and the result is: 1, 3, 29, , , ,

i want ignore empty cells and the result that i need is: 1, 3, 29

how to do?

thanks

 

[AhoyNC]

In Excel versions 2010 and higher try the TEXTJOIN function.

Book2
	A	B	C
1	1	3	29
2
3		1, 3, 29
Sheet1

Cell Formulas
Range	Formula
B3	B3	=TEXTJOIN(", ",1,A1:G1)

 

[jamiguel77]

Sorry i Use Excel 2016 and not see the function, thanks

any other way?

 

[Eric W]

TEXTJOIN is only available in Excel 2019 and above. Try this:

Book1
	A	B	C	D	E	F	G	H	I
1	1	3	29						1, 3, 29
2	4		6		7				4, 6, 7
3	6	5	4	3	2	1	0		6, 5, 4, 3, 2, 1, 0
Sheet13

Cell Formulas
Range	Formula
I1:I3	I1	=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1)," ",", ")

 

[jamiguel77]

Done!

Thanks