VBA to insert line break (Alt+Enter)

Hi all,

Another query...

I have cells set to character width of 40 with word wrap.

I have approx 1000 cells with approx 100 words in each. This file is exported into another program which does not pick up the word wrapping. As such, I need a way to insert line breaks wherever each line ends in each cell.

So basically. Rather than go through 10000 lines pressing Ctrl+Enter, I was hoping there is a quick way to achieve the same.

A bit of VBA I could run that would force line breaks in all cells within column B would be ideal?

As always. Any help appreciated!

Haha - sorry, that'll teach me to talk and type. Yeah Alt+Enter.

The cells are set to 40 width and word wrap is on, so really where each line ends as it is is where I need the break to be.

I guess another way is Break at the first space that occurs prior to 40 characters (to ensure words aren't split)?

Does that help?!?

Sample Sheet

Hi,

the column width is not equal to the number of characters by line, check it !

Hi..

Don't worry about that. The main thing is whether there is a way to force the break at the end of each line.

Anyone? ☺

See InStrRev function to find out the break position …

I've looked at that as you suggest, but I'm struggling to see how that could lead to the forced line break.

Sorry if I'm being obtuse, but would appreciate any further insight if you're able to provide any?

Hi Apo,

Thanks for your input on this and the other thread. You are right in that they are both connected to the same task.

I will have a look at this later - I can only access at work, so replied are sporadic!

Will update once I've had a read.

Hi again Apo,

Once again a perfect solution. I'd searched for a long time looking for a solution to this one and was beginning to think it was impossible.

Very grateful indeed for all you help in both of these threads.

Thanks again.