Normalize spacing in valid UK postcodes

Turn sw1a1aa into SW1A 1AA, for postcodes already checked as valid.

What you'll make

In Excel: sw1a1aa becomes SW1A 1AA.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1SUBSTITUTE(TRIM(A8)," ","")Takes out every space."sw1a1aa"
2UPPER( … )Turns letters into capitals. LET calls this p."SW1A1AA"
3LEFT(p,LEN(p)-3)Keeps everything except the last three characters."SW1A"
4&" "&RIGHT(p,3)Adds one space, then the last three."SW1A 1AA"
5LET( … )Works out p once, then uses it twice."SW1A 1AA"

Excel works from the inside out. Part 4 includes the space joined in with &.

=LET(p,UPPER(SUBSTITUTE(TRIM(A8)," ","")),LEFT(p,LEN(p)-3)&" "&RIGHT(p,3))

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 UK postcodes into the green column of the Template tab. The formatted postcodes fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This only fixes spaces and capitals. It doesn't check a postcode is real, so check foreign or odd ones first.

Microsoft's help: LET function