Back to blog
8 min read 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 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.

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
Custom fields with codes, long numbers and ranges added as columns.
  1. In step 4, choose XLSX (or ODS for LibreOffice).
  2. Click Start Export and download the file.
Export step 4, Export Format with XLSX selected among CSV, JSON, XLSX, ODS and XML
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, 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. 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.

With an AI agent

An AI agent connected through the WordPress MCP Adapter (see connecting Claude Code to WordPress) 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.

Get the free plugin on WordPress.org.

Dmitriy
About author

Dmitriy

WordPress Developer with 15+ years of commercial experience. Elite Author on ThemeForest and creator of custom WordPress plugins. Passionate about clean code, performance optimization, and building useful tools for the WordPress community.

JOIN THE CONVERSATION

Comments 0

Have a question or a tip to share? Leave it below.

Leave a comment

Your email address will not be published. Required fields are marked *

We store your name, email and comment to show and moderate it; your email is never published. Spam protection is provided by Cloudflare Turnstile and profile pictures by Gravatar. See our Privacy Policy.


Try the plugin with no risks!

New users get 1 month with access to all premium features. Experience the most powerful WordPress data engine today.

Download Now
Get PRO for FREE