How to Preserve Leading Zeros in Bank Statement Numbers in Excel

When you open a bank statement CSV or copy transaction tables into Microsoft Excel, values like check number 00142, sort code 08-92-29, or US routing number 021000021 frequently appear as 142 or corrupt into dates. This happens because Excel evaluates cell contents automatically upon entry. If a value consists entirely of numerals, Excel assigns it a general numeric data type, stripping leading zeros as mathematically redundant.

In financial bookkeeping, these numbers are identification codes, not quantities. Losing leading zeros breaks accounting reconciliation, check registry tracking, and bank feed imports into QuickBooks, Xero, or ERP systems.

Double-clicking a CSV file forces Excel to open it with default data-type inference, which immediately strips leading zeros before you can apply formatting. To prevent this, import the file through the Data tab:

1. Open a blank workbook in Microsoft Excel.

2. Navigate to the Data tab on the top ribbon and click "From Text/CSV".

3. Select your statement CSV file and click Import.

4. In the preview dialog, look at the "Data Type Detection" dropdown at the top. Change it from "Based on first 200 rows" to "Do not detect data types" (or click "Transform Data" to open Power Query).

5. In Power Query, select the columns containing check numbers, sort codes, or account references, right-click the header, choose "Change Type", and select "Text".

6. Click "Close & Load" to drop the uncorrupted data into your worksheet.

If your check numbers or reference codes have already lost their leading zeros in an existing sheet, you can restore them using the TEXT formula—provided the field has a known, fixed character count.

For example, if you know your bank check numbers are always 5 digits long and a check displays as 142:

You must not assume =TEXT() solves every truncated column. Be aware of these real-world limitations:

1. Variable-length identifiers: If your check sequence includes both 3-digit checks (e.g. 520) and 4-digit checks (e.g. 1042), applying =TEXT(A2, "0000") will corrupt check 520 into 0520.

2. Upstream truncation: If a previous software export or CSV export saved truncated values without reference to original width, the original text data is lost from the file.

3. UK Sort Codes: A sort code like 08-92-29 that was entered without hyphens becomes 89229 (5 digits). While =TEXT(A2, "00-00-00") can re-apply hyphenation, it requires verifying that the original code began with 08.

If you are manually entering or correcting individual reference numbers in Excel, type a single apostrophe (') before the first digit (for example, '00142).

The apostrophe instructs Excel to treat the subsequent characters strictly as text. The apostrophe remains visible in the formula bar but does not display in the spreadsheet cell or print on exports.

In the Excel (.xlsx) file from PDFtoRow, reference numbers, check numbers and other non-amount columns are stored as text cells, so their leading zeros are kept. A CSV file has no cell types at all: it contains the characters exactly as printed (for example 00142), but if you double-click the CSV, Excel may still strip the zeros when it opens it. Import the CSV with Method 1 above, or use the .xlsx file.