Combining every 4 cells into 1 without losing data

[charlie3113]

Hello,

I have a column with hundreds of cells, and I want to combine every four cells into a single cell, without losing data. Also, there should be a space between each string that is combined. So:

Hi
there
how
ru
I
am
OK
thanks
That
is
great
bye


I want to combine so that I get three cells that read:

Hi there how ru
I am OK thanks
That is great bye


And on and on until all of the hundreds of cells in the column have been so combined. So if I have 1,000 cells, I should end up with 250 combined cells. Any help is greatly appreciated! Thank you!

 

[bukimi]

You can add cell contents simply by & formula, so if "Hi" is A1, "there" is A2 and so on, you need only another cell with formula:
=A1&" "&A2&" "&A3&" "&A4
then copy formula down every 4th cell (let me know if you need help with doing that)

 

[charlie3113]

Thank you for the reply, but I'm wondering if there is a way to do this with a macro. My project is rather complicated, and I will need to do this same merge process more than 100 times in separate spreadsheets. So that's a lot of copying and pasting. If I could just run a macro on each sheet, it would save a ton of time. Thanks!

 

[bukimi]

Try this one:

Sub merger()
Dim lastrow As Long
Cells(1, 2).FormulaR1C1 = "=RC[-1]&"" ""&R[1]C[-1]&"" ""&R[2]C[-1]&"" ""&R[3]C[-1]"
lastrow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
For Row = 1 To lastrow Step 4
    Cells(1, 2).Copy Cells(Row + 4, 2)
Next Row
End Sub

Of course it assumes where specific cells are:
- your text is in column 1 row after row,
- there is 2nd column empty for merged values
You'd have to amend values if your data is elsewhere.