Excel – columns to one cell

Solution 1:

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

Solution 2:

• 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

Solution 3:

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

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