Build a military or diplomatic last line

Turn APO, AE and 09021 into APO AE 09021, with no city name.

What you'll make

In Excel: APO + AE + 09021 becomes APO AE  09021.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="APO",A8="FPO",A8="DPO")Checks the type is APO, FPO or DPO.Yes, APO
2OR(B8="AA",B8="AE",B8="AP")Checks the region is AA, AE or AP.Yes, AE
3A8&" "&B8&" "&C8Joins 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
5IF(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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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