Copy multiple cells to one cell

[Tigerxxx]

How can I copy data in multiple cells into one cell one after the other?
Example-
Cells A1=3, B1=4, C1=5......Y1=25, Z1=26
I want to copy the data in cells A1,B1 through Z1 into one single cell A2 as
3,4 ,5....26.

Please advise.

 

[Marcelo]

Try to concatenate

=a1&", "b1"& etc

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Tigerxxx" escreveu:

 

[Tigerxxx]

Thanks for your input Marcelo.

As I understand concatenate can only work for five cells at a time. I was
looking for some other convenient way for higher number of cells.

Thank you

 

[David Biddulph]

Which 2 cells are you omitting between C1 and Y1?

 

[David Biddulph]

You understand wrongly. What made you think that?

 

[Tigerxxx]

Hi David,

I am sorry...let me rephrase the example-
Example-
Cells A1=1, B1=2, C1=3......Y1=25, Z1=26
I want to copy the data in cells A1,B1 through Z1 into one single cell A2
i.e. I want to copy 26 cells into one single cell.

Please advise. Thank you.

 

[David Biddulph]

The answer is to use concatenation, as previously explained.
=A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1&", "&G1&", "&H1&", "&I1&",
"&J1&", "&K1&", "&L1&", "&M1&", "&N1&", "&O1&", "&P1&", "&Q1&", "&R1&",
"&S1&", "&T1&", "&U1&", "&V1&", "&W1&", "&X1&", "&Y1&", "&Z1

 

[Herbert Seidenberg]

Or if you don't want to do all that typing:
Copy A1:Z1
MS Word > Edit > Paste Special > Unformatted Text
MS Word > Edit > Find > Find what > ^t
Replace with > , > Replace All
Edit > Copy
MS Excel > Paste

Or if you want to stay in Excel
and dare to use iterate:
Tools > Options > Calculations > Manual
check iteration, set maximum to 26
C2 =0
B2 =IF(C2=0,1,B2+1)
A2 =IF(C2=0,"",A2&INDEX(1:1,B2)&",")
Change C2 to 1 and hit F9
The concatenated string will appear at A2
Select A2:B2 > Copy > Paste Special > Value
Uncheck iterate and set calculations to automatic

 

[Max]

Cells A1=1, B1=2, C1=3......Y1=25, Z1=26
I want to copy the data in cells A1,B1 through Z1 into one single cell A2
i.e. I want to copy 26 cells into one single cell.

Another option - try this sub by Gord Dibben

Sub ConCat_Cells()
'Gord Dibben .misc
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox _
("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.Text) > 0 Then sbuf = sbuf & y.Text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


---

 

[Tigerxxx]

Thanks a lot for your help guys!

Hi Max,
The macro will ber very helpful!

Thanks!

 

[Max]

Welcome, think Gord would be pleased to hear that as well !