none
how to concatenate cell by inserting comma in between RRS feed

  • Question

  • Hi all,

    I have cell range, i.e., A1:A100 filled in with numbers, how can I concatenate them and put commas in between, like

    1, 2, 3,...100

    thanks,

    Friday, January 17, 2020 4:17 PM

Answers

  • If you have Excel 2019 or Excel in Office 365:

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

    Otherwise, you might use a helper column.

    In B1, enter the formula =A1

    In B2, enter the formula =B1&", "&A2

    Fill or copy down from B2 to B100.

    B100 will contain the value you want. You can refer to this elsewhere by using =B100, and you can hide the helper column if you wish.

    A custom VBA function would be an alternative.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by sesquipedalian Tuesday, January 28, 2020 6:44 PM
    Friday, January 17, 2020 4:23 PM

All replies

  • If you have Excel 2019 or Excel in Office 365:

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

    Otherwise, you might use a helper column.

    In B1, enter the formula =A1

    In B2, enter the formula =B1&", "&A2

    Fill or copy down from B2 to B100.

    B100 will contain the value you want. You can refer to this elsewhere by using =B100, and you can hide the helper column if you wish.

    A custom VBA function would be an alternative.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by sesquipedalian Tuesday, January 28, 2020 6:44 PM
    Friday, January 17, 2020 4:23 PM
  • To: s...
    re:  concatenate cells

    Another method...
    The Concats function is part of the free Custom_Functions add-in.

    Download from MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    (files are now available with no ads)


    Saturday, January 18, 2020 1:17 AM
  • Hey Hans,

    thank you so much!

    Sheldon

    Tuesday, January 28, 2020 6:46 PM
  • Hi Hans,

    if the string ends up too wide, it might be an appeal to wrap it at a fixed width, say 10, like the the format

    1,2,3,4,5,6,7,8,9,10

    ,11,12,13,..          20

    ,21, ...,                30

    how can you make it happen through your formula?

    Wednesday, January 29, 2020 4:19 PM
  • I'll use the setup with the helper column.

    Change the formula in B2 to

    =B1&IF(MOD(ROW(),10)=1,CHAR(10),", ")&A2

    Fill down.

    Turn on Wrap Text for the cell in which you display the final result.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 29, 2020 4:29 PM
  • thanks a lot!

    Sheldon

    Wednesday, January 29, 2020 9:02 PM