How to keep leading zeros when opening a CSV in Excel
By
SpreadCheaters
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
OR
Right-click and select Format Cells from the context menu
OR
Go to Home > Cells > Format > Format Cells
OR
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
