{"id":1015,"date":"2026-10-23T17:33:12","date_gmt":"2026-10-23T17:33:12","guid":{"rendered":"http:\/\/jfdi-blog-csv-leading-zeros-excel"},"modified":"2026-10-24T13:46:31","modified_gmt":"2026-10-24T13:46:31","slug":"csv-leading-zeros-excel","status":"publish","type":"post","link":"https:\/\/jfdi.pro\/blog\/csv-leading-zeros-excel\/","title":{"rendered":"How to open a CSV in Excel without losing leading zeros"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Excel drops leading zeros because a CSV file has no column types, so Excel guesses, and anything that looks like a number becomes one. <code>00123<\/code> turns into <code>123<\/code>, 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&#8217;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.<\/p>\n\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"https:\/\/jfdi.pro\/convert\/\">Convert CSV to Excel, zeros kept<\/a><\/div>\n<\/div>\n\n\n\n<h2 class=\"wp-block-heading\">What goes wrong<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>In the CSV<\/th><th>After opening in Excel<\/th><\/tr><\/thead><tbody><tr><td><code>00123<\/code> (product code)<\/td><td><code>123<\/code><\/td><\/tr><tr><td><code>07700 900123<\/code> (UK mobile)<\/td><td>stays text only because of the space; <code>07700900123<\/code> becomes <code>7700900123<\/code><\/td><\/tr><tr><td><code>02134<\/code> (US ZIP code)<\/td><td><code>2134<\/code><\/td><\/tr><tr><td><code>4111111111111111<\/code> (16-digit number)<\/td><td><code>4.11111E+15<\/code>, and the last digit becomes 0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Fix 1: change Excel&#8217;s settings (Microsoft 365 and Excel 2024)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Newer versions of Excel let you switch off the conversions. On Windows go to <strong>File \u203a Options \u203a Data<\/strong>; on a Mac go to <strong>Excel \u203a Preferences \u203a Edit<\/strong>. Under <strong>Automatic Data Conversion<\/strong>, untick:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Remove leading zeros and convert to number<\/strong><\/li>\n\n\n\n<li><strong>Keep the first 15 digits of long numbers and display in scientific notation if required<\/strong><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Microsoft documents both in <a href=\"https:\/\/support.microsoft.com\/en-us\/excel\/set-automatic-data-conversions\">Set automatic data conversions<\/a>. The settings apply to every workbook you open from then on, so they&#8217;re the best fix if you handle codes and IDs all the time. Older versions of Excel don&#8217;t have them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Fix 2: import instead of opening<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Instead of double-clicking the CSV, open a blank workbook and use <strong>Data \u203a From Text\/CSV<\/strong>. In the preview, choose <strong>Transform Data<\/strong>, select the columns that hold codes, and set their type to <strong>Text<\/strong>. Then <strong>Close &amp; Load<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This works in any recent Excel, but you have to do it every time, for every column.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Fix 3: convert the CSV to an Excel file first<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <a href=\"https:\/\/jfdi.pro\/convert\/\">JFDI file converter<\/a> reads each value for what it is. Codes such as <code>00123<\/code>, 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.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Open the <a href=\"https:\/\/jfdi.pro\/convert\/\">file converter<\/a> and drop in your CSV (or a whole folder of them).<\/li>\n\n\n\n<li>Choose <strong>Excel<\/strong> under Convert to.<\/li>\n\n\n\n<li>Press <strong>Convert<\/strong>, then <strong>Download<\/strong>.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Fix 4: Google Sheets<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In Google Sheets, use <strong>File \u203a Import \u203a Upload<\/strong>, and untick <strong>Convert text to numbers, dates, and formulas<\/strong> before importing. Leading zeros and long numbers stay as written.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Already lost the zeros?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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 <code>=TEXT(A2,\"00000\")<\/code> for five-digit ZIP codes. Long numbers rounded to 15 digits can&#8217;t be recovered; go back to the original export.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Why it matters<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Lost leading zeros cause quiet failures: postcodes that don&#8217;t match, FIPS county codes that leave Alabama off a map, product codes that don&#8217;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&#8217;s the subject of <a href=\"https:\/\/jfdi.pro\/blog\/csv-dates-changing-excel\/\">why dates change when you open a CSV<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Related<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/jfdi.pro\/blog\/blog\/csv-dates-changing-excel\/\">Why dates change when you open a CSV, and how to stop it<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/jfdi.pro\/blog\/blog\/us-county-map-from-excel-fips\/\">How to map US county data from Excel<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/jfdi.pro\/help-convert.html\">The file converter guide: spreadsheets and data<\/a><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel turns 00123 into 123 and long ID numbers into 1.23E+15 when it opens a CSV. Here are four ways to stop it, from a one-time setting to converting the file first.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[32,33,34,35],"class_list":["post-1015","post","type-post","status-publish","format-standard","hentry","category-file-conversion","tag-csv","tag-excel","tag-leading-zeros","tag-spreadsheets"],"_links":{"self":[{"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/posts\/1015","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/comments?post=1015"}],"version-history":[{"count":2,"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/posts\/1015\/revisions"}],"predecessor-version":[{"id":1085,"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/posts\/1015\/revisions\/1085"}],"wp:attachment":[{"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/media?parent=1015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/categories?post=1015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jfdi.pro\/blog\/wp-json\/wp\/v2\/tags?post=1015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}