Handling Leading Zeros in Product IDs and Part Numbers

Leading zeros cause problems. Excel drops them. Databases expect them. Here's how to handle them correctly.

The Problem

Your part numbers look like this in the system:

Excel shows them as:

Now your SQL query returns zero results because 123456 doesn't match 00123456.

Solution 1: Fill Leading Zeros

If your IDs should be 8 digits, pad them:

Input:

Output (filled to 8 digits):

('00123456', '00789012', '00345678')

Set "Fill Leading Zeros" and specify the target length.

Solution 2: Trim Leading Zeros

If you need to match against a column that stores integers:

Input:

Output:

(123456, 789012, 345678)

Use "Trim Leading Zeros" to strip them.

Trim Modes

Integer Only: Only trims if the value is purely numeric

All: Trims leading zeros from any value

Common Formats

SQL Query Example

After padding to 8 digits:

SELECT product_name, stock_level
FROM inventory
WHERE part_number IN ('00123456', '00789012', '00345678')

Database Column Types

VARCHAR/TEXT: Store as strings, leading zeros preserved

INT/BIGINT: Store as numbers, leading zeros dropped

Know your schema before querying. If the column is VARCHAR, you need the zeros. If it's INT, you don't.