Share via

how do I convert column data to a comma separated list in a single cell

Anonymous
2020-04-29T18:23:51+00:00

How do I convert column data to a comma-separated list in a single cell?

For example:

From this:

          A                         B

1    168396

2    168444

3    168555

4    168567

5    168936

To this:

                                     A                                               B

1     168396,168444,168555,168567,168936

2

3

4

5

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-04-29T18:32:37+00:00

    Hello
    I am V. Arya, Independent Advisor, to work with you on this issue. Use Textjoin function
    =TEXTJOIN(", ",TRUE,A1:A5)

    20+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2020-04-29T19:58:45+00:00

    Hi W

    As V. Arya mentioned in the previous post I think you should try 

    =TEXTJOIN(", ",TRUE,A1:A5)

    Change the ranges in the formula according to your scenario.

    Regards

    10+ people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-04-29T18:28:27+00:00

    Also, if you have Office 365 Excel then you can use TEXTJOIN()

    and if you don't have then go to excel online and use TEXTJOIN() function and then copy-paste the data in your flile

    0 comments
  2. Anonymous
    2020-04-29T18:27:26+00:00

    You can convert rows to columns using the transpose option.

    Simply copy it, right click, and hit the fourth button to the right under the paste options.

    0 comments
  3. Anonymous
    2020-04-29T18:25:57+00:00

    You can first use Transpose function and then use joint operator < &> for putting in single cell.

    Else VBA Macro is required to get it done in single shot

    0 comments