Move the ZIP to a new line when too long

Boston MA 02108-1234 is too long for 15 characters, so the ZIP moves down.

What you'll make

In Excel: Boston MA 02108-1234 + 15 becomes Boston MA ¶ 02108-1234.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEN(A8&" "&B8&" "&C8)<=D8Checks the whole line fits the limit in column D.No
2A8&" "&B8&" "&C8The city, state and ZIP on one line.Not used for row 8
3AND(LEN(A8&" "&B8)<=D8,LEN(C8)<=D8)Checks city and state fit, and the ZIP fits.Yes
4A8&" "&B8&CHAR(10)&C8Puts the ZIP on its own line. ¶ marks a line break."Boston MA¶02108-1234"
5IF( … )Uses part 2, else part 4, else says Review."Boston MA¶02108-1234"

The Review message sits inside part 5. The ZIP+4 stays whole, hyphen and all.

=IF(LEN(A8&" "&B8&" "&C8)<=D8,A8&" "&B8&" "&C8,IF(AND(LEN(A8&" "&B8)<=D8,LEN(C8)<=D8),A8&" "&B8&CHAR(10)&C8,"Review postal line width"))

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 the city, state, ZIP and the longest line allowed into the green columns of the Template tab. The city lines fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Nothing is shortened. Turn on Wrap Text where you paste the results, so the ZIP shows on its own line.

Microsoft's help: IF function