0

I have the task of creating a simple Excel sheet that takes an unspecified number of rows in Column A like this:

1234
123461
123151
11321

And make them into a comma-separated list in another cell that the user can easily copy and paste into another program like so:

1234,123461,123151,11321

What is the easiest way to do this?

Raystafarian
  • 21,583
  • 11
  • 60
  • 89
Jeremy Bentham
  • 11
  • 1
  • 1
  • 2
  • 1
    I'm voting to close this question as off-topic because it is a plagiarized duplicate of a highly upvoted question. – fixer1234 Jan 25 '17 at 18:45

2 Answers2

0

Yes, I know this is a duplicate question. I'm sorry, I did everything wrong. But I have a great answer. I only signed up to I could post an answer to this question. But you needed a 10 reputation to answer the original question.

To solve this problem, don't use code and don't use CONCATENATE because it's too cumbersome.

Use the Excel formula TEXTJOIN

You can pick your delimiter

For example

=TEXTJOIN(",",,A1:A4)

Results in: 1234,123461,123151,11321

Yay! Please share this as I can't stand to see people struggle in Excel! It's amazing! Excel changed my life!

Jeremy Bentham
  • 11
  • 1
  • 1
  • 2
  • Realize that TEXTJOIN is only availbale in Office 365. And this has been answered many times with TEXTJOIN on this site as well as others. – Scott Craner Jan 25 '17 at 16:45
  • Also as TEXTJOIN is not an Array type formula and therefore it can use full column references, therefore as data is added to Column A, then it will automatically update, without the need to update the formula. But if the full column is used then the second criterion needs to be `TRUE` – Scott Craner Jan 25 '17 at 16:47
0

If you have Office 365 Excel then you can use TEXTJOIN():

=TEXTJOIN(",",TRUE,A:A)

If not then you need to use a helper column:

In B1 put:

=A1&","&B2

And copy down.

Then in another cell put:

=Left(B1,Len(b1)-1)

enter image description here

Scott Craner
  • 22,693
  • 3
  • 21
  • 25