How to force postcodes or phone numbers into one format

An input mask is the answer, and Base by Zaweba has ready-made ones for the UK. Give the text field a mask in the table designer and people can only type in that pattern. There are presets for postcode, phone, National Insurance number, sort code, time and car registration, and what people type is tidied as it is saved, so sw1a1aa becomes SW1A 1AA.

That means the records come out consistent without anyone having to remember the rules, which pays off every time you sort, search or print a list.

Picking a preset

Open the table in the designer, choose the text field, and set its mask. For most British address and contact data a preset does the job. A postcode field takes the postcode mask, a phone field the phone mask, and so on. Sort codes and National Insurance numbers are the classic cases where people type them five different ways, with spaces, with dashes, in lower case. The mask removes that choice.

Car registrations suit fleet lists, garage records and car club databases. The time mask is there for things like opening hours or appointment slots kept as text.

Writing your own mask

When there is no preset for what you need, you can write a mask from a short set of characters. A 0 means a digit must go there and a 9 means an optional digit. L stands for a letter, and A for a letter or a digit. A > turns what follows into capitals, and a backslash makes the next character literal, so it appears as typed rather than being read as an instruction.

That is enough for most house formats. A membership number made of two capital letters and four digits is >LL0000. A product code with a fixed prefix uses the backslash to keep the prefix as it is. Keep masks short and test them by typing a few real examples into the datasheet before you rely on them.

Masks, rules and dropdowns

A mask controls the shape of what is typed. It does not check that a postcode exists or that a phone number rings. If you need a value from a fixed list, a value list (a dropdown) is the better tool. If you need a range, such as a number from 1 to 100, use a validation rule. The three work well together on one table, each doing the job it is good at.

Masks belong on text fields. Numbers, money and dates already have their own types in Base, which refuse the wrong kind of value without any help.

Cleaning up what is already there

A mask guides new typing. Records that were entered before you added it, or that arrive from an old spreadsheet, may still be untidy. Sort the column in the datasheet to bring the odd ones together and fix them, or filter on the field to find the ones that start with a lower-case letter or contain a space in the wrong place. Once the table is clean, the mask keeps it that way.

On the website too

If you export the database as a PHP and MySQL website with File > Export to website, the site applies the same mask. Someone adding a member on their phone sees the same pattern and gets the same tidied result as you do on the desktop. That is the main reason to set masks in the table rather than relying on people to be careful: the table design travels with the data, wherever it is entered.

Base by Zaweba also has an AI helper, available only if you connect your own AI account. Its Suggest rules option can propose input masks along with validation rules for the open table, each listed with a reason, and you tick the ones you want.