UK National Insurance (NI) numbers should contain two prefix letters, six digits and one suffix letter.

E.g. QQ123456C

To validate a column containing NI numbers:

(1) Click the new column button

(2) Add the following custom expression:

my_column.uppercase().replace(" ","").extract("^[A-Z]{2}.*[0-9]{6}[A-Z]\$")

(where my_column is the NI number column).

(3) Click Done.

The new column will return NULL for incorrect NI numbers, or the NI number itself for correct ones 👨‍🚀

Did this answer your question?