Wrap a firm name at the last space

Acme Office Supply Company is too long for 20 characters, so Company moves down.

What you'll make

In Excel: Acme Office Supply Company + 20 becomes Acme Office Supply ¶ Company.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEN(A8)<=B8Checks the whole name fits the limit in column B.No
2TEXTBEFORE(LEFT(A8,B8+1)," ",-1)Keeps the words before the last space that fits."Acme Office Supply"
3MID(A8, … ,LEN(A8))Keeps every word after that space."Company"
4IFERROR( … )Says Review when no space fits the limit."Acme Office…¶Company"
5IF( … )Keeps a name that fits, otherwise uses part 4."Acme Office…¶Company"

CHAR(10) between parts 2 and 3 starts a new line. ¶ marks a line break.

=IF(LEN(A8)<=B8,A8,IFERROR(TEXTBEFORE(LEFT(A8,B8+1)," ",-1)&CHAR(10)&MID(A8,LEN(TEXTBEFORE(LEFT(A8,B8+1)," ",-1))+2,LEN(A8)),"Review no safe firm-name word break"))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own addresses. Paste firm names and the longest first line allowed into the green columns of the Template tab. The wrapped names fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only the first line is checked. Make sure the second line fits too, and turn on Wrap Text where you paste.

Microsoft's help: TEXTBEFORE function