Build a house number like N89W16786

Join N, 89, W and 16786 into N89W16786, with no spaces inside.

What you'll make

In Excel: N + 89 + W + 16786 becomes N89W16786.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="N",A8="S")Checks column A is N or S.Yes
2OR(C8="E",C8="W")Checks column C is E or W.Yes
3B8<>"",D8<>""Checks both number parts are filled in.Yes
4A8&B8&C8&D8Joins the four parts with no spaces."N89W16786"
5IF( … )Uses part 4 when parts 1 to 3 pass, else says Review."N89W16786"

AND needs parts 1 to 3 all to pass. The Review message sits inside part 5.

=IF(AND(OR(A8="N",A8="S"),OR(C8="E",C8="W"),B8<>"",D8<>""),A8&B8&C8&D8,"Review house number parts")

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. Use the green columns of the Template tab. Paste the N or S letter, first number, E or W letter and second number. The house numbers fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use the order your local records give. The formula joins the parts but never swaps them round.

Microsoft's help: IF function