Concat list in excel issue

Question

I want to concat a list in the excel. using this

=CONCATENATE(TRANSPOSE(J2:J9)&",")

J is the column of the list (J2 to J9) . K is where the concat takes place. But the results shown above, it is not e.g. a,b,c,d,f,da,a,sd ??

Help please has been trying to figure out whole day

what you are trying to achieve i really do not understand? and if you could provide sample file that will be great — Rajput
– Rajput, Commented Mar 15, 2021 at 11:33
CONCATENATE() does not take an array and implicit intersection will only return the 1st value. Use TEXTJOIN() instead > =TEXTJOIN(",",,J2:J9). — JvdV
– JvdV, Commented Mar 15, 2021 at 11:35
No it isn't, only from Excel 2019 onwards. You can look into an UDF, or spell all arguments out in CONCATENATE() > =CONCATENATE(J2,",",J3,","....etc) — JvdV
– JvdV, Commented Mar 15, 2021 at 11:46

Rajput · Accepted Answer · 2021-03-15 11:54:58Z

1

Then you can use this UDF function for Excel 2016.

=CONCATENATEMULTIPLE(J2:J9,",")

Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
 Result = Result & Cell.Value & Separator
Next Cell
CONCATENATEMULTIPLE = Left(Result, Len(Result) - 1)
End Function

edited Mar 15, 2021 at 11:54

answered Mar 15, 2021 at 11:35

Rajput

6054 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concat list in excel issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related