8

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

e.g.,

1
2
3
4
5

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

  • Could you copy the column, transpose it and then export as CSV? – kobaltz Mar 23 '12 at 22:08
7

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
4
  • 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
2

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

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

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.

1

Amazing answer, karthikeyan. I didn't want to waste time in VB either or even to escape from Ctrl+H. This would be most simplest, and I am doing this.

  1. Insert a new row on top (A).
  2. Just above number 1 (i.e. on A1), type an equals character (=).
  3. Copy/paste a comma (,) from B1 to B23 (not in B24).
  4. Select A1 to B24, copy/paste in Notepad.
  5. In Notepad press (Select All) Ctrl+A, press (Copy) Ctrl+C, then click inside a single cell in Excel (F2), then (Paste) Ctrl+V.
1

Notepad is the simplest and fastest. From Excel, I added in a column for running numbers in column A. My data is in Column B. Then copied column A & B to notepad. Removed the extra spaces by using the replace function in notepad. Copied from notepad and pasted back to excel in the single cell I wanted the data in. All done. No VB needed!

0

Best and Simple solution to follow:

Select the range of the columns you want to be copied to single column

Copy the range of cells (multiple columns)

Open Notepad++

Paste the selected range of cells

Press Ctrl+H, replace \t by \n and click on replace all

all the multiple columns fall under one single column

now copy the same and paste in excel

Simple and effective solution for those who dont want to waste time coding in VBA

  • 1
    It seems like you read just the title. If you read the whole question and look at the other answers, you'll see that this doesn't really relate to what was asked. – fixer1234 Nov 9 '15 at 16:59
0

The easiest way to combine list of values from a column into a single cell I have found to be using a simple concatenate formula. 1) Insert new column 2) Insert concatenate formula using the column you want to combine as the first value, a separator (space, comma, etc) as the second value, and the cell below the cell you placed the formula in as the third value. 3) Drag the formula down through the end of the data in the column of interest 4) Copy & paste special values in the newly created column to remove the formulas, and BOOM!...all values are now in the top cell.

For example, the following formula will combine all values listed in column A in cell C3 with a semi-colon separating them =CONCATENATE(A3,";",C4)

  • This duplicates other solutions, but suffers from leaving an extra delimiter at the end. Jerry Beaucaire's answer from 6 yrs ago is a working version of your approach. – fixer1234 Aug 21 '18 at 20:48
-1

Here's a quick way... Insert a column of ", " next to each value using fill. Place a CONCATE formula, anywhere, with both columns in the range, done. The result is a single cell, concatenated list with commas and a space after each.

  • I do not understand what you are saying —  and, if you fixed this to be understandable, it would probably end up being equivalent to one (no, several) of the answers already given. – Scott May 27 '17 at 1:11
  • @James I can't get this suggestion to work. In A1:A4 I have 1 2 3 4 (one value per row). In B1:B4, I have ", ". In C5 I have =concatenate(A1:B4). If that actually worked, it would be pretty cool, but it doesn't appear to. Were you picturing something else? – Adam Aug 11 '17 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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