Replacing merged cells

[PPettit]

I was handed a spreadsheet containing a number of columns with the first column consisting of merged cells separated by blank cells.

Is there an easy way to remove the "merged" formatting and replace all of the merged cells with single cells containing the data that was in the merged cell?
For example:
This...
[tt]
| A | B
--------------------
1 | example1 | text
2 | | text
--------------------
3 | |
--------------------
4 | example2 | text
5 | | text
--------------------
6 | |
--------------------
[/tt]
Would become this...
[tt]
| A | B
--------------------
1 | example1 | text
2 | example1 | text
--------------------
3 |
--------------------
4 | example2 | text
5 | example2 | text
--------------------
6 |
--------------------
[/tt]
What I'm trying to do is to make this spreadsheet easier to import into Access 2003. If there's a way to do this within Access during the import process, that would be great.

 

[thegrover]

Select the entire workship click once on the merge cell icon. All merged cells will reset.

 

[PPettit]

If I understand your reply correctly, this won't work for me. It does separate the merged cells but it doesn't fill the empty cells with data.

All of the merged cells do not contain data. The first cell in each grouping contains data, the rest do not. If all of the cells in the first column contained data, I wouldn't have a problem.

 

[xlhelp]

You are not going to be able to do it in one fell swoop.

I suggest that after unmerging, you add another column and put a formula in row 2 such as =IF(B2="",B1,B2) and copy down. then delete column B or whichever column has example1 example 2 etc.

Member- AAAA Association Against Acronym Abusers