Bypass justify

**timsoderstrom.se** · 11-01-2024, 11:20 AM

Hello,

I would like to write protocols in Excel to be able to store them all in the same file. What I haven't thought about is that the line breaks are adjusted strictly between the lines, which means that longer texts increase the line height so much that the text is moved to the next page. This means that virtually all pages contain too much empty space of rows.

Hence I have looked at the possibility of using the method justify in VBA, but the problem is that it only regulates up to 255 characters and discards the rest. Is there a way to bypass this with VBA? I want to store everything that is written in B1 in a named variable 'MyText' and ensure that VBA then picks out whole words up to the maximum limit of 255 characters, which then spills out to the cells below. After that to pick the next 255 characters from the MyText variable and then continue spilling where the previous string left off.

In Excel, I think the formula could look something like this, however, this is not with full words and without knowing in which row to put the next function:

LEFT(B1,255)

MID(B1,255+1,255)

MID(B1,255+255+1,255)

Is there anyone who had made a solution to this before or know how to do this in VBA?

Best regards
Tim

**RhiSJ** · 11-02-2024, 12:22 AM

Try this formula.
But it has a drawback, it can't distinguish between words, so it cuts through the middle of a WORD.

Formula:

Please Login or Register  to view this content.

**timsoderstrom.se** · 11-03-2024, 02:05 AM

Hi,

Thanks, interesting formula. I had to test it with 95 characters instead of 255 because the formula also spilled into cells on the right. However, I am looking for a solution in VBA that checks how many characters fit in the existing cell width before the text spills down, and unfortunately it has to be in whole words. That is, something that simulates Justify but with more characters than just its limitation of 255 characters. However, your formula could work if you only get all whole words and that no new lines start with spaces.

Best regards
Tim

**RhiSJ** · 11-03-2024, 03:23 AM

I made a UDF what you want.
It has two parameters, the first is TEXT to be splitted, the second is LENGTH of lines.
The second parameter is optional, could be omitted, it's default value is 95.

This UDF split line feed first, and split each line, all line's length are equal to or less then LENGTH.

Below two formula is equivalant.
=TextWrap(B1)
=TextWrap(B1, 95)

Please Login or Register  to view this content.

**timsoderstrom.se** · 11-03-2024, 05:02 PM

Hi,

Thanks, this is a very good solution. Even better than I imagined. I'm also impressed that you responded so quickly, especially when I hadn't seen anyone else have a solution on how to, as an alternative to Justify, spill more than 255 characters.

I am attaching a pdf of how nice this turned out. Thank you once again.

Protokoll ST.pdf

Best regards
Tim

**RhiSJ** · 11-03-2024, 08:07 PM

Thanks for your reputation.

Bypass justify

LinkBack

Thread Tools

Rate This Thread

Display

Bypass justify

Re: Bypass justify

Re: Bypass justify

Re: Bypass justify

Re: Bypass justify

Re: Bypass justify

Thread Information

Users Browsing this Thread

Similar Threads

Justify allignment

Excel 2007 : Using Fill Justify

Justify Method

Series & Justify?

Justify right

[SOLVED] Right Justify List Box Entries

[SOLVED] text and formula justify??

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions