Sift Data cleaning for CRM imports

Postcodes

Format UK postcodes in a CSV (Canadian postal codes too)

A UK postcode needs the single space in the right place and consistent casing: SW1A 1AA, not sw1a1aa and not SW1A1AA. Excel and manual entry mangle both, dropping the space, lower-casing the letters, or stripping a leading zero from anything that looks numeric. Sift finds the postcode column and standardizes it, so every row comes out in the same clean shape, free and in your browser.

Format your postcodes in Sift, free →

What goes wrong

In the fileCorrect format
sw1a1aa (UK, no space, lower case)→SW1A 1AA
k1a0b1 (Canadian, no space)→K1A 0B1
1234 (US ZIP, leading zero stripped)→01234

A UK postcode is two halves with a single space between them, and the space always sits before the final three characters. A Canadian postal code is six characters, letter digit letter, space, digit letter digit. When the space is missing, the case is inconsistent, or Excel has eaten a leading zero from a US ZIP code, your CRM sees the same address written several different ways, which breaks matching and dedupe.

The manual fix

  1. Reinsert the space with a formula that splits the postcode before its final three characters, then joins the two halves back with a single space.
  2. Upper-case the letters with a second formula wrapped around the first, so the whole column reads in capitals.
  3. Or buy a macro. There is a paid Excel macro sold specifically to format UK postcodes, which tells you how fiddly people find doing it by hand.
  4. Redo it every time. A CSV keeps no formatting, so the moment the file is reopened in Excel the spacing and casing can be re-mangled and a leading zero re-stripped, and you run the formulas again.

The Sift fix

  1. Load the CSV into Sift. It profiles every column in your browser and detects the one holding postcodes.
  2. Standardize UK postcodes: Sift puts the single space in the right place and upper-cases the letters, so SW1A 1AA comes out the same for every row.
  3. Format Canadian postal codes the same way, inserting the space in the middle and capitalizing the letters (K1A 0B1).
  4. Re-pad US ZIP codes that lost a leading zero, restoring 1234 back to 01234.
  5. Review with a diff, then export a clean CSV that is ready to import.
Sift is a static web app with no backend: your file is processed entirely on your device and never uploaded. Once the page has loaded it even works offline, which is an easy way to verify the privacy claim for yourself.

What Sift can't fix

Sift standardizes the shape and casing of a postcode, so it is consistent and import-ready. It does not check that the postcode is a real, deliverable address against an official database like Royal Mail PAF, and it will not invent a postcode that is missing. If a row has no postcode, or the wrong one, Sift cannot know that; it makes what is there consistent, not correct.

Related guides

Standardize country names for a CRM import Excel ruined my CSV: leading zeros, dates, and the fix that sticks Prepare a list for a CRM import Clean a CSV before importing into Salesforce All guides