0

I have a column of values and would like to get in one field a comma-concatenated string of those column. I did it indirectly with the following technique:

  • Suppose my list is A1:A50
  • In column C, for C1, I used =A1&","
  • For C2 I used =C1&A2&","
  • Then I auto-filled down
  • And so in C60 I had the whole comma-concatenated list of values.

Now, while this did the job, it's very primitive, making no use of any specialized formulas (if one exists for such a job).

I forgot to mention that I have already tried =TRANSPOSE(A1:A100) which is suggested everywhere on the internet, but the formula returns an error (probably due to my data having double-quotes)...

[map_lookup("getFireplace", {fireplace[1]})]
[map_lookup("getWc", {wc[1]})]
[map_lookup("getElevator", {elevator[1]})]
[map_lookup("getDevices", {devices[1]})]
[map_lookup("getStorage", {storage[1]})]
[map_lookup("getAc", {ac[1]})]
[map_lookup("getFurnitured", {furnitured[1]})]

This is a small extract of the column data I want to get as a comma-concatenated string.

So is there any more advanced/short way to achieve that? TIA

Faye D.
  • 833
  • 1
  • 3
  • 16
  • Did you try TEXTJOIN on the duplicate link? `=TEXTJOIN(",",TRUE,A1:A50)` – Scott Craner Apr 26 '22 at 20:34
  • I don't have Office365, I have Office 2019 – Faye D. Apr 26 '22 at 20:35
  • Then you either need to do what you are doing or use VBA, there is no other way. The link has a vba udf that mimic TEXTJOIN. – Scott Craner Apr 26 '22 at 20:36
  • What is the problem with TRANSPOSE returning an error? Do you have any idea? – Faye D. Apr 26 '22 at 20:37
  • Can you try the data I posted above and then do a =TRANSPOSE(A1:A7) ? – Faye D. Apr 26 '22 at 20:38
  • And my guess is that you hitting the limit of Transpose. It is ridiculously low. And 50 cells with that much data would come close to it. With the 7 you show I have no problem with transpose. – Scott Craner Apr 26 '22 at 20:41
  • And, just so you know there is a limit to the number of characters that a cell can hold. There are other ways if all you want is the output that use text editors. Save the file as a CSV open it in a text editor and replace the line breaks with a comma. The second most popular answer [HERE](https://superuser.com/questions/240858/convert-a-column-into-a-comma-separated-list/1171513#1171513) steps through it using word – Scott Craner Apr 26 '22 at 20:43
  • I'm not hitting the char limit. I'm sure about that for two reasons: a. My indirect way has absolutely no problem holding the data of 46 (that's the exact, current number of items in the column) items similar to the 7 ones I pasted here. b. I did a `=TRANSPOSE(A1:A3)` to the exact same data I pasted here, and it couldn't complete that one either... – Faye D. Apr 26 '22 at 20:47
  • 1
    BTW, Office 2019 has TEXTJOIN. At least it is listed in the version list: https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c – Scott Craner Apr 26 '22 at 20:49
  • OMG, TEXTJOIN did the job!!!!!!!!!!! – Faye D. Apr 26 '22 at 20:51
  • You're a gem my friend! Thank you so very much! :D – Faye D. Apr 26 '22 at 20:51

0 Answers0