Label a box or mailstop number by role

The role column decides the label: PO BOX, PMB or MSC. Nothing is guessed.

What you'll make

In Excel: Postal box + 123 becomes PO BOX 123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8Reads the delivery role for this row."Postal box"
2"Postal box","PO BOX "&B8A postal box gets PO BOX before the number."PO BOX 123"
3"Private mailbox","PMB "&B8A private mailbox gets PMB before the number.Not used for row 8
4"Mailstop","MSC "&B8A mailstop inside a company gets MSC.Not used for row 8
5SWITCH( … )Uses the part that matches the role."PO BOX 123"

Read it left to right. Any other role says Review box/mailstop role.

=SWITCH(A8,"Postal box","PO BOX "&B8,"Private mailbox","PMB "&B8,"Mailstop","MSC "&B8,"Review box/mailstop role")

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 each role (Postal box, Private mailbox or Mailstop) and its number into the green columns of the Template tab. The box lines fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Take each role from your records; never guess it from the number. Then add the rest of the address.

Microsoft's help: SWITCH function