Search code examples
excelexcel-2007

Excel concatenate fuction for whole column

I need a solution for this:

A     B
1.    1,2,3,4,5,,,
2.
3.
4.
5.

so i want to concatenate A column like this: 

(A2;",";A3;",";A4;",";A5;",";A6;",";A7;","; and so on)

I want to remove commas (,) behind the number if they're less column - for example 5 and i concatenate 7.

How can i do that ?

Solution

  • there might be a better way of doing this but i will share my "dumb" way anyway.

    In B1 i will make it = A1 In B2 =SUBSTITUTE(B1&","&A2,".","") and i just need to fill down the column to the last row

    you can get your final result at column B, last row This is to prevent repetitive selecting all the cells in the whole list (using concatenate)