Build a military or diplomatic last line
Turn APO, AE and 09021 into APO AE 09021, with no city name.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | OR(A8="APO",A8="FPO",A8="DPO") | Checks the type is APO, FPO or DPO. | Yes, APO |
| 2 | OR(B8="AA",B8="AE",B8="AP") | Checks the region is AA, AE or AP. | Yes, AE |
| 3 | A8&" "&B8&" "&C8 | Joins type, region and ZIP, two spaces before the ZIP. | "APO AE 09021" |
| 4 | "Review military fields" | Shown when a code isn't on those lists. | Not used for row 8 |
| 5 | IF(AND( … )) | Uses part 3 when both checks pass, otherwise part 4. | "APO AE 09021" |
Read it left to right. AND needs both checks to pass. Capitals in the codes don't matter.
=IF(AND(OR(A8="APO",A8="FPO",A8="DPO"),OR(B8="AA",B8="AE",B8="AP")),A8&" "&B8&" "&C8,"Review military fields")How 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 post office types, region codes and ZIP codes into the green columns of the Template tab. The last 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 only checks the codes. Copy each code and ZIP from the person's own record.
Microsoft's help: IF function
OTHER ROUTES YOU MIGHT LIKE

Build a Canadian address line
Ottawa + ON + K1A 0B1 → OTTAWA ON K1A 0B1
Get the template →
Build a U.S. city-state-ZIP line
Boston + MA + 02108 → BOSTON MA 02108
Get the template →
Keep a unit on the street delivery line
12 OAK ST + APT 5 → 12 OAK ST APT 5
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →