Skip to main content

Search

Let us guide you!

Create an ID with a Prefix and Leading Zeros (ID0042)

Updated on

Do you want to display a readable and consistent identifier, such as ID0042 or CLI-00042 - Dupont? This article presents formula combinations to add a prefix and pad a number with zeros.

Key point: the output format must be Text. In the Options tab of your formula column, set the Output Format to Text. If set to Number, leading zeros are removed (0042 becomes 42). See Formula Column.

Padding a Number with Zeros

The FORMAT_NUMBER function, with a zero mask, pads the number on the left.

FORMAT_NUMBER($Number, "0000")

The number of zeros in the mask sets the width: "0000" for 4 digits (42 becomes 0042), "00000" for 5 digits.

Adding a Prefix

Combine with CONCAT to attach a prefix before the number.

CONCAT("ID", FORMAT_NUMBER($Number, "0000")) → ID0042

More complete example, with a separator and the client name:

CONCAT("CLI-", FORMAT_NUMBER($Number, "0000"), " - ", $Client) → CLI-0042 - Dupont

Variant: Keep Only the Last Digits

To keep only the last n digits of a number, use RIGHT after padding with zeros.

RIGHT(CONCAT("0000", $Number), 4)
Beware of duplicates. Two different numbers can have the same last digits. Reserve this variant for short display purposes, not for an identifier that must remain unique.

The Case of RowID

ROWID() returns the internal row ID: a large number. FORMAT_NUMBER(ROWID(), "0000") therefore does not add zeros, since the number already has more than four digits. For a short, sequential identifier (ID0001, ID0002, ID0042), use a counter field starting at 1, rather than RowID.

Learn More

FORMAT_NUMBER

The number formatting function and its masks.

Read more →

Learn More

Formula Column

The formula editor and output format (Text, Number, Date...).

Read more →