Copy Merged Cell w. Multiple Lines to Multiple Cells in One Column

[c.clavin]

Hi all, I'm trying to take a merged cell that has multiple lines (in this case 8) and turn/paste it into 8 single cells in the same column. Is this even possible? I'm taking a PDF and converting it to an Excel file, but its making each of the columns into one big, merged cell. I've seen the 'Text to Columns' is a way that many split merged cells, but I dont think it would work in my case. I've attached an image to show what I've got and what I want to turn it to. Thanks.

 

[MickG]

Try this for results in column "B".

[COLOR="Navy"]Sub[/COLOR] MG01Apr09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Variant, Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]If[/COLOR] Dn.MergeCells And Dn.Address = Dn.MergeArea(1).Address [COLOR="Navy"]Then[/COLOR]
    Sp = Split(Dn.MergeArea.Cells(1, 1), vbLf)
       [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Sp
             c = c + 1
             Cells(c, "B") = R
        [COLOR="Navy"]Next[/COLOR] R
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick