How can I split merged cells and distribute the contents of the merged cells so that each unmerged cell has the contents of it's parent merged cell?

Select the cell, the range of cells, or the entire column that contains the text values that you want to divide across other cells. A range can be any number of rows tall, but no more than one column wide.
 Important   Unless there are one or more blank columns to the right of the selected column, the data to the right of the selected column will be overwritten.

On the Data menu, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

You can swap rows and columns words in solution..

Is this what you are looking for?

Thanks for your answer, unfortunately I think your answer is for splitting something inside cells using some character that is in all the cells to split, for example to split a name contained into one cell (John Doe) into two cells one with (John) and the other with (Doe).

What I need to do is the following: If I have 3 cells in a column that are merged and the merged cell contains the name John Doe, I would like to split the cell but instead of getting only one John Doe in the upper cell I would like each of the three cells filled with John Doe. That is the most basic description for the problem, the full problem is better explained in the original question.

Thanks again and I hope you can help me.

Adrian

select the 3 merged columns, right click on selection, goto Format Cells option, select Alignment tab, click on merge cells option until the check box is empty.

This will unmerge the columns, keeping their values in the left column only. Copy & paste the left column into the second & third column.

I think I understand your issue now:

Some rows have merged columns (ie merged columns A to C, or B to C) and some of them aren't?
So when you unmerge all the rows, you get some empty cells in columns B & C?

If I've understood you correctly, do the following:

select all the data (merged & unmerged rows), unmerge all of them (goto Format Cells option, select Alignment tab, click on merge cells option until the check box is empty).

run the macro below to fill in empty cells (if cells B & C are empty, the macro fills them with values from column A, if only cells C are empty, it is filled with values from cells B).

Is this what you are looking for?

Sub FillInBlanks()
Dim rng As Range, i As Double
Set rng = Selection
For i = 1 To rng.Cells.Count
    If IsEmpty(rng.Cells(i)) Then
        If rng.Cells(i).Column <> rng.Columns(1).Column Then
            rng.Cells(i) = rng.Cells(i).End(xlToLeft)
        End If
    End If
Next i
End Sub

Select allOpen in new window

Hi! kittenwhisky

Thanks for your answer, unfortunately it's now what I need.

I'll try to be more specific using a much simple example

-In will use only one column
-I am using 1500 rows
-I have 500 vertically merged cells, each merged cell spans 3 rows (In the real problem the merged cells spans ARE VARIABLE this is just a way to illustrate the problem).
-Each merged cell has a the name of a different person (in the real problem there are also numbers and punctuation) so I have 500 names in 500 merged cells and since each merged cell spans 3 rows I am using 1500 rows.

What I need is to unmerge the 500 cells so that I will get 1500 normal cells but each unmerged cell will be replaced by three normal cells WITH THE NAME OF THE PERSON that was in the original cells.
So for each person I will get 3 cells (each in a row) that will replace the original merged cell and each cell will have the name of the person that was in it's original merged cell.

Thanks again and I hope you can help me.

Adrian

forgot to mention: don't forget to select the data in the particular row before running the macro.

forgot to mention: don't forget to select the data in the particular COLUMN before running the macro.

I got your problem completely.

I ran a small version with three merged cells spanning three rows. (9 cells in total)

I unmerged one by one i got 3 filled cells and 6 unfilled and i dragged and copied but this is not what you want because you definitely dont want to do it manually so you need a macro for this for sure.

kittenwhisky macro looks effective to me though

sorry for multiple posts, I tweaked the macro just a little to allow for multiple column selection. So you can select range A2:C4999 once, unmerge all cells, and whilst the selection is still active, run the macro. It should fill in all the blanks for all columns correctly.

Sub FillInBlanks()
Dim rng As Range, i As Double
Set rng = Selection
For i = 1 To rng.Cells.Count
    If IsEmpty(rng.Cells(i)) Then
        If rng.Cells(i).Row <> rng.Rows(1).Row Then
            rng.Cells(i) = rng.Cells(i).End(xlUp)
        End If
    End If
Next i
End Sub

Select allOpen in new window

The solutions needs to be followed in several steps, but it works fine. Also I had never used excell marcros code before. But I guess that is my bad