---
title: "Excel Shows #### or Wrong Dates, Lost Leading Zeros and Scientific Notation: Fixing an Exported CSV"
url: "https://wpimportexport.com/excel-leading-zeros-dates-scientific-notation-csv/"
description: "Excel drops leading zeros, shows E+15 or turns 3-12 into a date when you open a WordPress CSV export. Export to XLSX or import columns as text."
type: post
published: 2026-11-09T06:24:41+00:00
modified: 2026-11-14T10:42:43+00:00
categories: ["Export Guides", "Tutorials"]
tags: ["CSV import", "Import Export by RockStarLab", "WordPress export"]
docs_topics: ["Data, formats and functions", "Export guides"]
---

# Excel Shows #### or Wrong Dates, Lost Leading Zeros and Scientific Notation: Fixing an Exported CSV

You export orders, products or posts with custom fields from WordPress, open the CSV in Excel, and the values change: the ZIP code `02108` becomes `2108`, the customer number `00123` becomes `123`, a 16-digit tracking number shows as `1.23457E+15`, a size range `3-12` turns into a date, and a date column shows `########`. The file itself is correct. Excel converts the values when it opens the file. [Import Export by RockStarLab](https://wordpress.org/plugins/import-export-by-rockstarlab/) can export a file that keeps them, and a CSV you already have can be opened without losing data.

**Free:** CSV, XLSX and ODS export of posts, pages, comments and media with their custom fields. **PRO:** the same for WooCommerce products and orders, users, terms and custom post types, where SKUs, barcodes and phone numbers usually live.

## Short Answer

- Export to **XLSX** instead of CSV. Codes with leading zeros, long numbers and values like `3-12` arrive as text cells, so Excel doesn’t change them, while plain numbers like quantities stay numbers you can sum.
- For a CSV you already have, don’t double-click it. Use **Data → From Text/CSV** and set the affected columns to **Text** before loading.
- In Microsoft 365, you can turn off Excel’s automatic conversions in its options.
- `########` on its own only means the column is too narrow: double-click the column border to widen it.

Exports only read your content, so you don’t need a backup for them.

## What Excel changes, and why

A CSV has no cell types. Every value is plain text, and when you double-click the file, Excel guesses a type for each value. Our test file had three orders:

```
post_title,meta_zip_code,meta_customer_number,meta_tracking_number,meta_barcode,meta_size_range,meta_total,post_date
"Order 1003: Milan",20121,00789,1111222233334444,8001234567895,10-11,120,"2026-11-07 09:00:00"
"Order 1002: Boston",02108,00456,9876543210987654,5901234123457,1-2,18.50,"2026-11-07 09:00:00"
"Order 1001: Hamburg",20095,00123,1234567890123456,4006381333931,3-12,49.90,"2026-11-07 09:00:00"
```

Opened by double-click in Excel with US settings, it changes like this:

| Column | In the file | Excel shows | Why |
| --- | --- | --- | --- |
| zip_code | 02108 | 2108 | Read as a number; leading zeros dropped |
| customer_number | 00123 | 123 | Same |
| tracking_number | 1234567890123456 | 1.23457E+15, stored as 1234567890123450 | Excel keeps only 15 significant digits; the 16th becomes 0 |
| barcode | 4006381333931 | 4.00638E+12 | Numbers over 11 digits are shown in scientific notation in the General format |
| size_range | 3-12 | 12-Mar | Looks like a month and day |
| total | 49.90 | 49.9 | Trailing zero is formatting, not data |
| post_date | 2026-11-07 09:00:00 | ######## | Converted to a date and time that is wider than the column |

Lost zeros and the 16th digit are gone for good once the file is saved again from Excel, so fix this before anyone edits and re-saves the file. Microsoft describes the 15-digit limit in [Keeping leading zeros and large numbers](https://support.microsoft.com/en-us/office/keep-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7).

With other regional settings the dates differ (`3-12` may become 3 December), but the effect is the same.

## Fix 1: Export to XLSX

An Excel workbook stores a type for every cell, so Excel doesn’t have to guess.

1. Go to **Import Export → Export** and choose the content type and filters.
2. In step 3, add the columns you need, including the custom fields with codes and numbers.

![Export step 3, Export File Structure with Title, zip_code, customer_number, tracking_number, barcode, size_range, total and Date columns](https://wpimportexport.com/wp-content/uploads/2026/11/01-columns-codes-and-numbers.webp)

_Custom fields with codes, long numbers and ranges added as columns._

3. In step 4, choose **XLSX** (or **ODS** for LibreOffice).
4. Click **Start Export** and download the file.

![Export step 4, Export Format with XLSX selected among CSV, JSON, XLSX, ODS and XML](https://wpimportexport.com/wp-content/uploads/2026/11/02-xlsx-format.webp)

_XLSX keeps codes as text and plain numbers as numbers._

The export decides the cell type for each value:

- **Number cells** for plain numbers that read back exactly the same: `120`, `20121`, `-12`, `0.5`. You can sort, filter and sum them.
- **Text cells** for everything else: `00123` and `02108` (leading zero), `1234567890123456` and `4006381333931` (more than 11 digits), `49.90` (trailing zero), `3-12`, phone numbers like `+4930123`, and dates.

In our export, the Boston ZIP code stayed `02108`, the tracking numbers kept all 16 digits, the barcodes kept all 13 digits, and the totals `120` could be summed. If a column mixes both kinds, such as ZIP codes `20121` and `02108`, both display correctly; select the column and choose **Text** as the number format if you want them all aligned the same way.

Dates are written as text in the same `2026-11-07 09:00:00` format as in WordPress, so they don’t shift with time zones or regional settings and import back unchanged. To calculate with them, select the column and use **Data → Text to Columns → Finish**, and Excel converts them to real dates.

For very large exports (tens of thousands of rows), CSV is lighter for the server; see [exporting a large site without timeouts](https://wpimportexport.com/export-large-wordpress-site-without-timeouts/), then use Fix 2 to open the file.

## Fix 2: Open a CSV with Text columns

When the file must be a CSV, open it through the import dialog instead of double-clicking:

1. In Excel, open a blank workbook and go to **Data → From Text/CSV**.
2. Select the file and click **Transform Data** (not Load).
3. In the Power Query editor, select the columns with codes, long numbers and ranges (hold Ctrl or Cmd), then choose **Data Type: Text**. If Excel asks, choose **Replace current**.
4. If Excel already added a **Changed Type** step under Applied Steps, delete it first, so the original values come back.
5. Click **Close & Load**.

The older **Text Import Wizard** does the same. On Windows, turn it on under **File → Options → Data → Show legacy data import wizards**, then use **Data → Get Data → Legacy Wizards → From Text**: in step 3, select each column and choose **Column data format: Text**.

Google Sheets: use **File → Import**, upload the file, and clear **Convert text to numbers, dates, and formulas**. LibreOffice Calc shows a Text Import dialog on opening a CSV; set the affected columns to **Text** in the column type list.

## Fix 3: Turn off automatic conversions in Excel

Excel for Microsoft 365 has options to stop these conversions for every CSV you open. On Windows, go to **File → Options → Data → Automatic Data Conversion**; on a Mac, **Excel → Preferences → Edit**. Clear the conversions you don’t want:

- **Remove leading zeros and convert to number**
- **Keep the first 15 digits of long numbers and display in scientific notation if required**
- **Convert digits surrounding the letter “E” to a number in scientific notation**
- **Convert continuous letters and numbers to a date**

See Microsoft’s [Set automatic data conversions](https://support.microsoft.com/en-us/Excel/set-automatic-data-conversions). Older Excel versions don’t have these options; use Fix 1 or Fix 2 there.

## Importing the file back

If someone edits the file in Excel and you import it with Import Export by RockStarLab, the importer writes each value as it appears in the file. A file that went through Fix 1 or Fix 2 keeps `00123` and all 16 digits. A CSV that was opened by double-click and saved again already contains `123` and `1.23457E+15`, and no import can bring the lost digits back, so re-export from WordPress instead of re-importing that file. Before any import that changes your content, [create a backup](https://wpimportexport.com/how-to-create-a-wordpress-website-backup-and-restore-it/).

## With an AI agent

An AI agent connected through the WordPress MCP Adapter (see [connecting Claude Code to WordPress](https://wpimportexport.com/connect-claude-code-to-wordpress-mcp/)) can choose the format for you. A prompt:

```
Export the posts whose title contains "Order 100" with zip_code,
customer_number, tracking_number, barcode, size_range, total and date
to a file my accountant can open in Excel without losing leading zeros
or long numbers.
```

In our run, the agent called `rsl-ie/export-start` with `"format": "xlsx"`, a `post_title contains "Order 100"` filter and the eight columns, waited for the job with `rsl-ie/job-get` and returned the file from `rsl-ie/file-get`. The workbook had 3 rows: `00123`, `02108`, the 16-digit tracking numbers and `49.90` were text cells, and `120` and `20121` were number cells.

## FAQ

### Why does Excel remove leading zeros from my CSV?

A CSV has no cell types, so Excel reads `00123` as the number 123. Export to XLSX, where such codes are text cells, or open the CSV with Data → From Text/CSV and set the column to Text.

### Why does a long number end with 0 or show E+15?

Excel stores numbers with 15 significant digits. A 16-digit card, tracking or order number loses its last digit and is shown in scientific notation. Keep such values as text: export to XLSX or import the column as Text.

### What does ######## mean in an Excel column?

The value, usually a date or a long number, is wider than the column. Double-click the right border of the column header to widen it.

### Why does a size like 3-12 turn into a date?

Excel reads it as month and day. In an XLSX export it’s a text cell and stays `3-12`; in a CSV, set the column to Text when you open it.

### Can I still sum prices and quantities in the XLSX file?

Yes. Plain numbers such as `120` or `0.5` are number cells. Values with a trailing zero, like `49.90`, are text so they stay exactly as stored; select the column and use Data → Text to Columns → Finish to turn them into numbers if you need to calculate with them.

## Related Guides

- [Broken Characters and Emojis in an Exported CSV: UTF-8 and BOM for Excel](https://wpimportexport.com/csv-utf-8-bom-excel-broken-characters/)
- [Exported CSV Opens in One Column in Excel: Delimiter Fixes](https://wpimportexport.com/csv-opens-in-one-column-excel-delimiter/)
- [CSV, JSON, XML, XLSX and ODS: What Data Formats Are and When to Use Them](https://wpimportexport.com/csv-json-xml-xlsx-and-ods-what-data-formats-are-and-when-to-use-them/)
- [How to Export All WooCommerce Orders into CSV or Excel](https://wpimportexport.com/how-to-export-all-woocommerce-orders-into-csv-or-excel/)
- [Export WordPress Pages to CSV: Titles, URLs, Parent and Template](https://wpimportexport.com/export-wordpress-pages-csv/)

Get the free plugin on [WordPress.org](https://wordpress.org/plugins/import-export-by-rockstarlab/).
