Combining every 4 cells into 1 without losing data

charlie3113

New Member
Joined
Apr 12, 2017
Messages
2
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

Board Regular
Joined
Apr 12, 2017
Messages
82
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

New Member
Joined
Apr 12, 2017
Messages
2
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

Board Regular
Joined
Apr 12, 2017
Messages
82
Try this one:
Code:
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.
 

Forum statistics

Threads
1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top