Formula to Combine Range on row to One Cell Separated by Comma

[Legacy 436357]

Hello,

Is there a formula to combine cells on the same row (A2:H2) with each value separated by comma and one space ignoring any blank cells?

Thank you

 

[Fluff]

If you have TextJoin you can use
=TEXTJOIN(", ",TRUE,A2:H2)

 

[Legacy 436357]

I have excel 2010 sorry it isn't available

 

[kweaver]

If you don't have TEXTJOIN, here's a UDF that I think will do it: ConcatRange(A2:H2,", ")

Function ConcatRange(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
 If Not (IsEmpty(Cell.Value)) Then
  Result = Result & Cell.Value & Separator
 Else
 End If
Next Cell
ConcatRange = Left(Result, Len(Result) - 2)

 

[Legacy 436357]

How is that function implemented please? I am not familiar.

 

[kweaver]

ALT+F11 and place the code in a MODULE. If you don't have a module, right click in the VBA editor on ThisWorkbook and insert a module, then paste the code.

Then, from a cell, use it like any other function: =ConcatRange(A2:H2,", ")

Sorry, my last line of the function didn't copy...try this:

Function ConcatRange(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
 If Not (IsEmpty(Cell.Value)) Then
  Result = Result & Cell.Value & Separator
 Else
 End If
Next Cell
ConcatRange = Left(Result, Len(Result) - 2)
End Function

 

[Legacy 436357]

Thanks I get Expected End Function error

 

[kweaver]

See my post 6

 

[Legacy 436357]

Thanks it works with the 'End Function' at the end.

 

[Rick Rothstein]

I have excel 2010 sorry it isn't available

You are only processing eight cells on the row, so if you wanted to stay with a formula solution then you could use this one...

[table="width: 500"]
[tr]
	[td]=MID(IF(A2="","",", "&A2)&IF(B2="","",", "&B2)&IF(C2="","",", "&C2)&IF(D2="","",", "&D2)&IF(E2="","",", "&E2)&IF(F2="","",", "&F2)&IF(G2="","",", "&G2)&IF(H2="","",", "&H2),3,999)[/td]
[/tr]
[/table]