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!!

1 reply

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,

Let's see if I can help you.

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 Integer
rC = 0
x = -9
Do
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
0