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

Question

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 · Answer

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

How to split a cell based on #27 line break

1 response

Similar discussions

Subscribe To Our Newsletter!