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.
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.
Using a User Defined Function will much more flexible than hard-coding cell by cell
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
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.
You could use the concatenate function and alternate between cells and the string ","
:
=CONCATENATE(A1,",",A2,",",A3,",",A4,",",A5)
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.
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.
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!
Its very simple with 2 steps. First put comma in each cell of the source column (using concatenate function) and then combine all row values into one cell (using CONCAT function).
First make a new column with concatenating with comma by putting below formula =concatenate(A1,",") Drag this new column formula until the last row, so that you will get all values with comma.
In the cell where you want the final values, put below formula and you are done: =CONCAT(B1:B28)
All above answers would do the trick but in my opinion, this would be the correct solution,
=TEXTJOIN(",",TRUE,A:A)
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
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)
1.Select all rows and copy
2.Paste special > Transpose
3.Select all new rows and copy
4.Paste to notepad(now we have single row in notepad separated by space)
5.Select all copy and paste to single excel cell
6.Replace space with comma in excel
First need to add comma after one for one cell (Like this 1,) then give Control + E for flash fill. After that just give concat formula,(=concat(A1:A5))
.
Here is an answer that doesn't require VB and accepts a range, while ignoring empty cells.
=TEXTJOIN(", ", TRUE, TRANSPOSE(FILTER(A1:A40, A1:A40<>"")))
Result:
1, 2, 3, 4, 5, ...
If you want to take it a step further and add some single quotes (maybe your writing a SQL Query Where clause or something...
=TEXTJOIN(",", TRUE, CONCATENATE("'"&TRANSPOSE(FILTER(A1:A40, A1:A40<>""))&"'"))
Result:
'1', '2', '3', '4', '5', ...
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.
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?