Conditional CONCAT function

[miva0601]

Hello,
I'm looking for an excel formula (or macro) that would look at the columns of each row and if there is a value it would concatenate it with the next column that has a value and add a space. Using only the CONCAT function results results in extra spaces, since it's not guaranteed that there will be a value in each column.

Book11
	A	B	C	D	E	F	G
1	Customer	BPR	GCR	GPR	HM		Desired Result
2	2699			GPR		GPR	GPR
3	4229		GCR			GCR	GCR
4	7347			GPR	HM	GPR HM	GPR HM
5	12951		GCR		HM	GCR HM	GCR HM
6	16171				HM	HM	HM
7	19069			GPR		GPR	GPR
8	24466	BPR		GPR		BPR GPR	BPR GPR
9	63447			GPR		GPR	GPR
10	714010	BPR		GPR	HM	BPR GPR HM	BPR GPR HM
11	760825	BPR			HM	BPR HM	BPR HM
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=CONCAT(B2," ",C2," ",D2," ",E2)

 

[jasonb75]

If you have CONCAT, then you should have TEXTJOIN

=TEXTJOIN(" ",1,C2:E2)

 

[Fluff]

Whilst I'd go with the textjoin function, if you want to use concat you could do it like

=TRIM(CONCAT(B2," ",C2," ",D2," ",E2))