Excel - columns to one cell

Question

Can you have a column of data moved to a single cell with commas separating the values that were in the column? Kind of reverve text to columns.

ie;

1

2

3

4

5

1,2,3,4,5 in a single cell.

Answer 1

• First, do

  =concatenate(A1,",")
  

  in the next column next to the one you have values.

• Second, copy the whole column and go to another sheet do Paste Special-> Transpose.

• Thirdly copy the value you just got, and open a word document, then choose Paste Options -> choose "A",
• Last, copy everything in the word document back to a cell in an excel sheet,you would get all values in one cell

Answer 2

You could use the concatenate function and alternate between cells and the string ",":

=CONCATENATE(A1,",",A2,",",A3,",",A4,",",A5)

Answer 3

Using a User Defined Function will much more flexible than hard-coding cell by cell

• Press alt & f11together to go to the VBE
• Insert Module
• copy and paste the code below
• Press alt & f11together to go back to Excel

use your new formula like the one in the D6 cell snapshot
=ConCat(A1:A5)

You can use more complex formulae such as the one in D7
=ConCat(A1:A5,A7:A24)
or D8 whihc is 2D
=concat(A1:B5,A7:A24)

Function ConCat(ParamArray rng1()) As String
    Dim X
    Dim strOut As String
    Dim strDelim As String
    Dim rng As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCnt As Long
    strDelim = ", "
    For lngCnt = LBound(rng1) To UBound(rng1)
        If TypeOf rng1(lngCnt) Is Range Then
            If rng1(lngCnt).Cells.Count > 1 Then
                X = rng1(lngCnt).Value2
                For lngRow = 1 To UBound(X, 1)
                    For lngCol = 1 To UBound(X, 2)
                        strOut = strOut & (strDelim & X(lngRow, lngCol))
                    Next
                Next
            Else
                strOut = strOut & (strDelim & rng2.Value)
            End If
        End If
    Next
    ConCat = Right$(strOut, Len(strOut) - Len(strDelim))
End Function

Answer 4

If it's a looong column of values, you can use the CONCATENATE function, but to do it quickly is a little tricky. Assuming the cells were A1:A10, in B9 and B10 put these formulas:

B9: =A9&","&B10

B10: =A10

Now, copy B9 and paste in all the cells UP to the top of column B.

In B1 you will now have you full result. Copy > Paste Special > Values.