CODEWITHSUNDEEP
excel
Joseph Kreifels II
Joseph Kreifels II

Reputation: 573

Excel is ruining my numbers with leading zeros

We process hundreds of shipments from FedEx, and the tracking numbers go into a CSV. Everything went fine for years until one of our customers decided to use FedEx Smartpost.

The tracking numbers for Smartpost are rather long. Here is one of them: 61299990387420002000

When this number is placed in Excel, it becomes 61299990387420000000

We end up with hundreds of shipments all stuck with that false tracking number. It seems Excel is converting the last half of the tracking number to all zeros.

Has anyone encountered this issue, and tips?

I have tried formatting the cell, no luck.

I am serious, copy and paste the first number in Excel and watch Excel automatically ruin the number

Upvotes: 1

Views: 187

Answers (3)

eshwar
eshwar

Reputation: 694

I have encountered this problem and I avoided VBA code by making my database system prefix the tracking number with an "F" so that Excel reads it as a text field.

Upvotes: 1

xQbert
xQbert

Reputation: 35333

Based on comments made (Scott Craner nailed it in a comment), I think you're directly opening the CSV in excel. You can't do this. Excel will do it's best to interpret each data type. since the tracking numbers are numbers it will bring them in as general and then you encounter the feature mentioned in earlier answers (@JosephKreifelsII err... yours...). To work around this you must IMPORT the document. DO NOTsimply open the CSV in Excel.

To save some headache you may even want to go as far as removing the .csv file extension association from Excel so you're forced to import it. (Tie it to notepad or notepad ++ or some other text editor instead!)

You must open an excel document then IMPORT the CSV

  1. Open Excel
  2. Go to Data Tab
  3. Select from Text
  4. Select csv file.
  5. select import button
  6. select delimited
  7. select next button
  8. unselect tab and select comma
  9. select next button
    1. define the data type for each of the columns (text for your tracking no)
    2. Select finish. Data will now be in excel without losing the last numbers.

enter image description here

You may be able to record a macro to do this, I've not tried.

Upvotes: 4

Joseph Kreifels II
Joseph Kreifels II

Reputation: 573

https://support.microsoft.com/en-us/kb/269370

Microsoft confirms this is purposely done.

IN order to avoid, you must preformat the cell to TEXT, and only then successfully paste the number in.

Our CSVs are created from the database all programatically. It's output as unformatted. So there isn't much we can do.

I've tested opening the CSV with notepad++, and things looked fine. The only option is to NOT look at the file with Excel

Upvotes: 0

Related Questions