# How to split a cell based on #27 line break

Closed
julish17 Posts 1 Registration date Thursday October 3, 2013 Status Member Last seen October 3, 2013 - Oct 3, 2013 at 10:47 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Oct 8, 2013 at 11:41 AM
Hi All!

First to all thanks a lot for your assistance! :)

I'm trying to split a cell into two based on the # of linefeeds that it contains.

I need to see the whole information into the cell without having to go to the formula bar. Using Arial 10, I can have 28 lines in one cell (expanded to 409.5).

So, from line break 29 going foward I need to cut that information and paste it into a new cell that I've added. How can I make VBA identify that text?

Thanks!!

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 8, 2013 at 11:41 AM
Hi Julish,

Start by creating one cell as you would like to see it. Now determine the length of the data in that cell.
Use this for that:
MsgBox Len([a1])

Let's say the answer is 10. This means you would like to chop your data in pieces of 10.
Use this for that:
`Sub RumMe()Dim rC, x As IntegerrC = 0x = -9Do    rC = rC + 1    x = x + 10    Range("B" & rC) = Mid([a1], x, 10)Loop Until x > Len([a1])End Sub`

Now your data has been split up in column B.

Hopefully it is useful.

Best regards,
Trowa