Power Query Problems

Keep Leading Zeros in ZIP Codes with Excel Power Query

Amos GikundaAmos Gikunda Nov 22, 2026 868 views

Question details

Keep or restore leading zeros in ZIP codes imported from a CSV file using Power Query.

Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A user imports CSV data containing ZIP codes and needs five-digit codes to retain their leading zeros.
Observed behavior
Power Query can interpret ZIP codes as numbers and remove leading zeros. Standard cell formatting does not restore the original text values.
Before you start

Open the query in Power Query Editor and check the ZIP column and Applied Steps for an automatic type conversion. Confirm that the affected entries are intended to be five-digit ZIP codes before padding them.

Solution 1Recommended

Restore five-digit ZIP codes with Text.PadStart

Use a custom column to add leading zeros when ZIP codes have already been converted to numbers.

This method pads values to at least five characters; it does not shorten longer values. Use it only when the intended codes are known to be five digits, because lost zeros cannot otherwise be recovered with certainty.

1
Open the query

In Excel, select Data > Queries & Connections, then double-click the relevant query to open Power Query Editor.

2
Add a custom column

Select Add Column > Custom Column and name the new column ZipCode. Use a different name if ZipCode already exists.

3
Enter the padding formula

In the Custom column formula box, enter: if [Zip] = null then null else Text.PadStart(Text.From([Zip]), 5, "0"). Replace Zip with the actual source column name, then select OK.

4
Set the type and load

Select the new ZipCode column, choose Transform > Data Type > Text, and select Home > Close & Load.

Restore five-digit ZIP codes with Text.PadStart
Check special ZIP formats: Review ZIP+4 codes, blanks, and non-ZIP values separately. The formula leaves null values unchanged but does not validate ZIP codes.
Free Microsoft Office alternative

Work with ZIP code spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easier move between apps. It does not fix Excel Power Query or run the Power Query M solution described above.

Open and review spreadsheets containing ZIP codesCompatible with Microsoft Excel formats such as XLSX and XLSFamiliar spreadsheet interface for everyday editingFree and lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why did Power Query remove the leading zero from a ZIP code?

An automatic or manually applied numeric data type can convert a value such as 01234 to 1234. In Power Query Editor, inspect Applied Steps for a Changed Type step and set the ZIP column to Text before numeric conversion.

Can I restore a leading zero by changing the Excel cell format?

A custom five-digit cell format can display a numeric value with a leading zero, but it does not restore the original text value. Use Text.PadStart in Power Query when you need the resulting ZIP code stored as text.

What if my ZIP column is already numeric?

In Power Query Editor, select Add Column > Custom Column and use Text.PadStart(Text.From([Zip]), 5, "0"), replacing Zip with your column name. Handle nulls separately if the column contains them.

Will Text.PadStart change ZIP+4 codes?

Text.PadStart adds characters only when a value is shorter than the specified length; it does not truncate longer values. Check ZIP+4 entries separately to ensure they retain their intended format.