Build a U.S. city-state-ZIP line
Turn Boston, MA and 02108 into BOSTON MA 02108 for envelopes and labels.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | UPPER(TRIM(A8)) | The city in capitals, without stray spaces. | "BOSTON" |
| 2 | &" "& | Adds one space. | "BOSTON " |
| 3 | UPPER(TRIM(B8)) | The state in capitals, without stray spaces. | "BOSTON MA" |
| 4 | &" "& | Adds two spaces before the ZIP. | "BOSTON MA " |
| 5 | C8 | Adds the ZIP code exactly as typed. | "BOSTON MA 02108" |
Read it left to right. The ZIP is kept as text, so a leading zero stays.
=UPPER(TRIM(A8))&" "&UPPER(TRIM(B8))&" "&C8How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own addresses. Paste cities, two-letter states and ZIP codes into the green columns of the Template tab. The lines fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
The formula doesn't check that a ZIP belongs to its city. Give new addresses a quick look.
Microsoft's help: UPPER function
OTHER ROUTES YOU MIGHT LIKE

Keep a unit on the street delivery line
12 OAK ST + APT 5 → 12 OAK ST APT 5
Get the template →
Write an address as a single sentence
Street, city, state, ZIP → 12 OAK ST, BOSTON, MA 02108
Get the template →
Build a PO Box delivery line
123 → PO BOX 123
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →