How to split a cell based on #27 line break

Closed
Report
Posts
1
Registration date
Thursday October 3, 2013
Status
Member
Last seen
October 3, 2013
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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