Function Problems

How to Create an Excel Document Number with Leading Zeros

Khadija Khan Oct 7, 2026 868 views

Question details

The user needs an Excel formula to combine fixed text, a dynamic string from a cell, and a sequential number formatted with leading zeros into a single document number string.

How to Create an Excel Document Number with Leading Zeros

Product: Excel
Device & OS: not provided
Scenario: Generating structured document numbers or custom IDs (such as MRC-AMP-0001) by combining values located in separate cells.
Observed behavior: When combining separate cells, Excel naturally drops the leading zeros from sequential numbers unless explicitly instructed to retain a specific number format.

Before you start

Ensure your document type codes and sequence numbers are organized in separate columns, and verify that the sequence values are entered as standard numbers before applying the concatenation formula.

Solution 1Recommended

Use the Ampersand (&) Operator and TEXT Function

The most efficient way to combine text and numbers while preserving leading zeros is to use the ampersand operator alongside the TEXT function to dictate the exact digit format.

The ampersand (&) operator links multiple strings and cell values together, while the TEXT function ensures the numeric sequence retains a fixed length by adding leading zeros where necessary.

Select the destination cell

Click on the cell where you want the final combined document number to appear, such as C2.

Enter the combination formula

Type the formula ="MRC-"&A2&"-"&TEXT(B2,"0000") directly into the cell or the formula bar, assuming A2 holds the document type and B2 holds the sequence number.

Apply the formula to the column

Press Enter to generate the first document number, then click and drag the fill handle at the bottom-right corner of the cell down the column to automatically format the remaining rows.

Use the Ampersand (&) Operator and TEXT Function

Understanding the formula syntax: The TEXT(B2, "0000") segment forces Excel to display the number as exactly four digits. If B2 contains '1', it will be formatted as '0001'.

Solution 2

Use the CONCATENATE Function

If you prefer using formal text functions over operators, you can nest the TEXT function inside a CONCATENATE (or CONCAT) function to achieve the same result.

Generate Document Numbers Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced text operations and the TEXT function, allowing you to dynamically create custom document numbers, product codes, and IDs just like you would in Microsoft Excel.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your document codes and sequence numbers.
2. Input the text formatting formula: Select the cell for your final output and type the formula: ="MRC-"&A2&"-"&TEXT(B2,"0000").
3. Confirm the result: Press Enter to view the combined text, ensuring the leading zeros are correctly preserved.
4. Drag to apply to other cells: Use the fill handle at the bottom right of the cell to drag the formula down, instantly generating document numbers for your entire list.

100% compatible with Microsoft Excel formulas and .xlsx file formatsEasily combine text, dates, and formatted numbers in secondsLightweight software that runs smoothly even on older devicesFree to use for all your daily spreadsheet tasks

Frequently Asked Questions

Why do leading zeros disappear when I type them into a spreadsheet?

By default, spreadsheets treat standard numerical entries as mathematical values, dropping leading zeros since they do not change the number's actual value. To keep them, you must format the cell as Text before typing, use an apostrophe (') before the number, or format it using the TEXT function.

Can I adjust the formula to create a six-digit sequence instead of four?

Yes. To change the total number of digits, simply adjust the number of zeros in the quotation marks within the TEXT function. For a six-digit sequence, change TEXT(B2, "0000") to TEXT(B2, "000000").

How do I use spaces instead of hyphens in my document number?

You can replace the hyphens within the quotation marks with spaces. For example, your modified formula would look like this: ="MRC "&A2&" "&TEXT(B2, "0000"). Make sure the space is enclosed between the quotation marks.

Excel & Data Problems