Keep Leading Zeros in ZIP Codes with Excel Power Query
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.
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.
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.
In Excel, select Data > Queries & Connections, then double-click the relevant query to open Power Query Editor.
Select Add Column > Custom Column and name the new column ZipCode. Use a different name if ZipCode already exists.
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.
Select the new ZipCode column, choose Transform > Data Type > Text, and select Home > Close & Load.

Import the ZIP column as text
Use this approach when the original CSV still contains the leading zeros and you want to preserve them during import.
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.

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.




