Split cells long text with Max 40 character to multiple row using Macro

[chee1012]

Help needed.

I have a large excel file with ~50K lines to limit one of the cells long text to multiple rows.
Example is attached.

I have try to put up a macro but I have problem to split the cells with proper word splitting.

Thanks.

[JapanDave]

Try this,

Please Login or Register  to view this content.

Edit* I just re-read your post, this won't work for over 4000 lines in the array. But if you can tell me if this macro does what you need, I will amend the code so you can use it on 50,000+ lines.

[nilem]

as an option

Please Login or Register  to view this content.

[chee1012]

Thanks Japan Dave and nilem for you help.

However, JapanDave's file, i believe it still needs some modification to work.
And nilem's macro file work perfectly. I use this to run now.

Just 1 last question for nilem, i try to copy the code into my excel file. It shows error on the command "Split" but it work perfectly in your file.
What is the reason? and how to make it work in my excel??

[newbi004]

Hi Nilem

Your code is brilliant! Could you explain how it's built/structured because I would like to adapt it for my project. I have a textbox in a userform which has a long text and I want to do what your code does without the additional cells.

Thanks