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
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 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
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!