Excel adding zeros to large numbers

Question

I have csv pipe delimited files, that when converted to xlsx, have three zeros added to only large numbers. How do I stop this?

Example: |243233597526377476| turns into 243233597526377476000

Answer 1

This is a classic Excel issue, and nothing is “wrong” with your data.

Why this happens

Excel can only store numbers accurately up to 15 digits.

Your value:

 

243233597526377476 (18 digits)

 

When Excel imports the CSV, it forces large numbers into scientific/rounded form, and when saving to  .xlsx , it pads with zeros, giving:

 

243233597526377476000

 

Excel is treating the value as a number, not an identifier.

 

✅ Correct solutions (choose one)

✅ Best solution: Import as TEXT (recommended)

This preserves the exact value.

Method 1: Excel Import Wizard

Open Excel (blank workbook)

Data → From Text/CSV

Select your pipe-delimited file

Click Transform Data

Select the column

Change Data Type → Text

Close & Load

✔ No zeros added

✔ Exact values preserved

 

✅ Alternative: Use Text-to-Columns

If already opened:

Select the affected column

Data → Text to Columns

Choose Delimited → Next

Select Other and type  | 

Next → set column format to Text

Finish

 

✅ Quick workaround (if you can edit the CSV)

Prefix large numbers with an apostrophe:

 

|'243233597526377476|

 

Excel will keep it as text.

 

✅ If you must automate (Power Query / scripts)

Ensure the column is explicitly defined as Text before conversion to  .xlsx .

 

❌ What will NOT work

Formatting the column as Number

Increasing decimal places

Custom numeric formats

Once Excel has corrupted the number, formatting cannot fix it.

 

✅ Key takeaway

If the value is:

IDs

Transaction numbers

Phone numbers

Hashes / references

👉 Always import as TEXT

 

If you want, tell me:.

Answer 2

Hi, 

Thank you for reaching out and for clearly sharing your experience. 

The reason the large numbers lose their last digits is that Excel stores numbers with a maximum of 15 significant digits, so any value with 16 or more digits gets rounded and padded with zeros. You can change this behavior by turning off the automatic data conversion that truncates long numbers. 

Please follow these steps: 

1. Select File, then Options. 
2. Select Data from the left side of the Excel Options window. 
3. Under Automatic Data Conversion, clear the box for Keep first 15 digits of long numbers and display in scientific notation if required. 

 

4. Select OK, then close and reopen Excel so the change takes effect. 
5. Open your pipe delimited file again. Values such as 243233597526377476 will stay intact instead of having their last digits replaced with zeros. 

I hope this information is helpful. Please follow the steps above and let me know if it works for you. If not, we can continue working together to resolve the issue.  

Should you have any questions or need additional assistance, please don’t hesitate to reach out so I can continue to support you. If you found the response useful, please consider marking it as accepted, as this may help other community members who are looking for similar guidance.  

Thank you for your patience and understanding. I look forward to continuing the conversation.  

---

If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.