How to keep leading zeros when opening a CSV in Excel

You can watch a video tutorial here.

Excel is frequently used for creating or opening CSV (Comma Separated Values) files. CSV files are text files in which the data is separated or delimited by commas. Each line in the file is a record and in each record, the field is separated by commas. The records become rows and the fields become columns when opened in Excel. Whenever a number in a CSV file is preceded by zeros, Excel ignores the zeros when the file is opened in Excel. It is possible to create and save a CSV file with leading zeros in Excel, but to retain the zeros, the file should be opened in another application such as Notepad, and then copied to Excel.

Step 1 – Open the CSV file with Notepad

– Find the saved file in Windows Explorer
– Right-click and select Open with > Notepad 

Step 2 – Format the Excel workbook as text

– Open a blank Excel workbook
– Select the sheet
– Open the Format Cells window: Go to Home > Number  and click on the arrow to expand the menu
Right-click and select Format Cells  from the context menu
Go to Home > Cells > Format > Format Cells
Press Ctrl+1
– On the Number tab select Text under the category
– Click OK

Step 3 – Copy and Paste the contents of the CSV file

– Copy the contents of the CSV file using Ctrl+C
– Paste the contents into the Excel workbook using Ctrl+V
– Leading zeroes are retained in Excel