0

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

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

enter image description here

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
Tyra
  • 105
  • 3
  • 12
  • what you are trying to achieve i really do not understand? and if you could provide sample file that will be great – Rajput Mar 15 '21 at 11:33
  • @Hafiz a,b,c,d,f,da,a,sd in the K column – Tyra Mar 15 '21 at 11:34
  • 1
    `CONCATENATE()` does not take an array and implicit intersection will only return the 1st value. Use `TEXTJOIN()` instead > `=TEXTJOIN(",",,J2:J9)`. – JvdV Mar 15 '21 at 11:35
  • @JvdV i dont think textjoin is available in excel 2016? – Tyra Mar 15 '21 at 11:44
  • No it isn't, only from Excel 2019 onwards. You can look into an [UDF](https://stackoverflow.com/a/39532855/9758194), or spell all arguments out in `CONCATENATE()` > `=CONCATENATE(J2,",",J3,","....etc)` – JvdV Mar 15 '21 at 11:46

1 Answers1

1

Then you can use this UDF function for Excel 2016.

=CONCATENATEMULTIPLE(J2:J9,",")

enter image description here

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
Rajput
  • 605
  • 3
  • 12