JFDI Blog

Tag: Excel

  • How to open a CSV in Excel without losing leading zeros

    Excel drops leading zeros because a CSV file has no column types, so Excel guesses, and anything that looks like a number becomes one. 00123 turns into 123, phone numbers lose their first 0, and long ID numbers get rounded to 15 digits. To stop it, either turn off the conversion in Excel’s options, import the file with the column set to Text, or convert the CSV to an Excel file that already stores those columns as text.

    Convert CSV to Excel, zeros kept

    What goes wrong

    In the CSVAfter opening in Excel
    00123 (product code)123
    07700 900123 (UK mobile)stays text only because of the space; 07700900123 becomes 7700900123
    02134 (US ZIP code)2134
    4111111111111111 (16-digit number)4.11111E+15, and the last digit becomes 0

    The last row is the nastiest. Excel stores numbers to 15 significant digits, so the sixteenth digit is replaced with a zero for good. Save the file and the original number is gone.

    Fix 1: change Excel’s settings (Microsoft 365 and Excel 2024)

    Newer versions of Excel let you switch off the conversions. On Windows go to File › Options › Data; on a Mac go to Excel › Preferences › Edit. Under Automatic Data Conversion, untick:

    • Remove leading zeros and convert to number
    • Keep the first 15 digits of long numbers and display in scientific notation if required

    Microsoft documents both in Set automatic data conversions. The settings apply to every workbook you open from then on, so they’re the best fix if you handle codes and IDs all the time. Older versions of Excel don’t have them.

    Fix 2: import instead of opening

    Instead of double-clicking the CSV, open a blank workbook and use Data › From Text/CSV. In the preview, choose Transform Data, select the columns that hold codes, and set their type to Text. Then Close & Load.

    This works in any recent Excel, but you have to do it every time, for every column.

    Fix 3: convert the CSV to an Excel file first

    The JFDI file converter reads each value for what it is. Codes such as 00123, phone numbers and postcodes stay exactly as written, and numbers longer than 15 digits are kept as text with every digit. Real numbers, dates and percentages still become numbers and dates, so you can sort and calculate with them.

    1. Open the file converter and drop in your CSV (or a whole folder of them).
    2. Choose Excel under Convert to.
    3. Press Convert, then Download.

    The workbook also gets a bold header row that stays put when you scroll, filter buttons and sized columns. Nothing is uploaded: the conversion runs in your browser, which matters when the file is a customer list.

    Fix 4: Google Sheets

    In Google Sheets, use File › Import › Upload, and untick Convert text to numbers, dates, and formulas before importing. Leading zeros and long numbers stay as written.

    Already lost the zeros?

    If the file has been saved after Excel removed them, the zeros are gone from the file. For codes with a fixed length you can add them back with a formula, for example =TEXT(A2,"00000") for five-digit ZIP codes. Long numbers rounded to 15 digits can’t be recovered; go back to the original export.

    Why it matters

    Lost leading zeros cause quiet failures: postcodes that don’t match, FIPS county codes that leave Alabama off a map, product codes that don’t find their stock. The same habit of guessing types is behind Excel turning text into dates, which famously corrupted gene names in published research. That’s the subject of why dates change when you open a CSV.

    Related

  • Why dates change when you open a CSV, and how to stop it

    Dates change when you open a CSV because the file stores everything as plain text, and Excel guesses what each value is. Anything that looks a bit like a date becomes one: 1-2 turns into 2 January, the gene name MARCH1 into 1 March, and 03/04/2025 into either 3 April or 4 March depending on your computer’s region settings. The fix is to stop Excel guessing, or to convert the file with something that reads the whole column before deciding.

    Convert CSV to Excel, dates handled properly

    Three ways dates go wrong

    1. Text that isn’t a date becomes one

    Excel converts values such as 1-2, 3/4, JAN1 or SEPT1 into dates. For part numbers, survey codes or scores, that’s corruption, and saving the file makes it permanent.

    The best-known case is genetics. A 2016 study in Genome Biology looked at the spreadsheets supplied with 3,587 papers in 18 journals and found gene names converted to dates in 19.6% of them (Pesquisa FAPESP). In 2020 the committee that names human genes gave up and renamed some: SEPT1 became SEPTIN1 and MARCH1 became MARCHF1, so Excel would leave them alone.

    2. Day and month swap

    03/04/2025 means 3 April in the UK and March 4 in the US. Excel reads it using your computer’s region setting, so the same file gives different dates on different computers. Worse, it’s inconsistent within a file: on a UK-set computer, a US-format 03/25/2025 can’t be a valid day-first date, so it stays as text while 03/04/2025 beside it becomes 3 April. You end up with a column that’s half dates and half text, with the dates silently wrong.

    3. Dates become numbers

    Excel stores dates as a count of days since 1900. When a date column ends up formatted as a number, or a date is copied to another program, you see values like 45748 instead of 1 April 2025.

    Fix 1: switch off the guessing in Excel

    In Excel for Microsoft 365 and Excel 2024 there’s a setting for exactly this. On Windows go to File › Options › Data; on a Mac go to Excel › Preferences › Edit. Under Automatic Data Conversion, untick Convert “date-like” combinations of continuous letters and numbers into a date.

    Microsoft notes a limit: values with a space or punctuation, such as JAN 1 or JAN-1, may still become dates (Microsoft). For full control, use the next fix.

    Fix 2: import the file and set the column types

    Open a blank workbook, choose Data › From Text/CSV, then Transform Data. Set code columns to Text. For real dates, right-click the column header and choose Change Type › Using Locale, then pick the date format the file actually uses, such as English (United States) for month-first dates.

    Fix 3: convert the file to Excel first

    The JFDI file converter looks at the whole column before deciding what a date means. If any value in it is like 25/03/2025, which can only be day-first, it reads the whole column day-first. Only if a column is truly ambiguous does it fall back to your browser’s language, and you can set it yourself under Reading the file › Dates like 03/04/2025 mean.

    Real dates become real Excel dates you can sort and calculate with. Codes that only look like dates stay as text, and a column that mixes dates with other text keeps those values as text so nothing is lost.

    When it writes CSV files, dates come out as 2025-03-31 (the ISO 8601 format), which every program reads the same way.

    How to write dates so this never happens

    If you produce CSV files for other people:

    • Write dates as YYYY-MM-DD. 2025-04-03 can’t be misread, and it sorts correctly as text.
    • Keep codes unambiguous. A prefix or letters elsewhere (PN-0102 rather than 1-2) avoids accidental dates.
    • Say what each column is in a header or a readme, especially for IDs.

    Related