Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Could you copy the column, transpose it and then export as CSV? –  kobaltz Mar 23 '12 at 22:08
add comment

4 Answers 4

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

  • Press & together to go to the VBE
  • Insert Module
  • copy and paste the code below
  • Press & together 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)

sample

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
share|improve this answer
add comment
  • 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
share|improve this answer
add comment

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

=CONCATENATE(A1,",",A2,",",A3,",",A4,",",A5)
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.