Numbers in Cells

[MNJ1193]

Hi all....

In my excel spreadsheet I have UPC code numbers that look like 737214535560 and so on. I normally format those cells to Number - Special - Zip Code. But everytime I open the spreadsheet, It opens them truncated with an E ---- 7337215E+11 ----)Normally, I when this happens I format the cells to to Number - Special - Zip Code and it usually converts to the origianl number.

However, This last time I opened it, this does not work and all numbers stay truncated.

I know I have to change these manually back to the original numbers, but how do I prevent this from happening again?

Thanks!

 

[Joe4]

Welcome to the Board!

It works just fine for me. What format are you saving the file in after you change the format of the cell?
Are these values hard-coded values, or formulas/links from elsewhere?
Do you have any VBA code on the file?
If you look at the format of the cell after opening the file back up, is it still set to "Zip Code", or something else like "General"?
Have you tried entering the value as Text instead?

 

[MNJ1193]

Welcome to the Board!

It works just fine for me. What format are you saving the file in after you change the format of the cell?
Are these values hard-coded values, or formulas/links from elsewhere?
Do you have any VBA code on the file?
If you look at the format of the cell after opening the file back up, is it still set to "Zip Code", or something else like "General"?
Have you tried entering the value as Text instead?

Hi.

I save it as "Excel Workbook (*xlsx)

I have no idea what you are talking about with the other questions you asked about hard coded values, formulas , links... I usually copy and paste the number ie... 737512358454 into the cell from another spreadsheet

When I re-open the sheet, and check the cell formatting it is back at General
Have not enetered as text..again, I copy and paste from a different excel sheet

 

[MNJ1193]

Hi.

I save it as "Excel Workbook (*xlsx)

I have no idea what you are talking about with the other questions you asked about hard coded values, formulas , links... I usually copy and paste the number ie... 737512358454 into the cell from another spreadsheet

When I re-open the sheet, and check the cell formatting it is back at General
Have not enetered as text..again, I copy and paste from a different excel sheet

Welcome to the Board!

It works just fine for me. What format are you saving the file in after you change the format of the cell?
Are these values hard-coded values, or formulas/links from elsewhere?
Do you have any VBA code on the file?
If you look at the format of the cell after opening the file back up, is it still set to "Zip Code", or something else like "General"?
Have you tried entering the value as Text instead?

I gave you the wrong info before. I am working in a CSV file

 

[Joe4]

I gave you the wrong info before. I am working in a CSV file

That is what I thought. Note that a CSV file is VERY different than an Excel file. A CSV file is a Text file, not an Excel file. That means it is a straight data file, and no formulas, formatting, etc are stored along with it.

However, if you are changing the format and saving as a CSV file, you probably have the file working exactly as you need, you just don't realize it.
The big mistake you are making is opening/viewing the final CSV file with Excel. When you do that, Excel will apply it is own setting/conversions to the data upon opening the file. So when you try to view a CSV in Excel, you are NOT seeing truly how the data looks in the CSV file.

For this reason, NEVER use Excel to view the contents of a CSV file if you truly want to see how the data is stored in the CSV file. Open the CSV file in a Text Editor like NotePad, and you will see the data as it truly resides in that file. If you do that, I think you will see that the number appears exactly as you expect it to.

One of my biggest complaints about Microsoft is that they determined that Excel should be set as the default program to view CSV files. This is a horrible decision, IMO, for this exact reason we are seeing. One of the first things I do when I get a new computer is reset that default so that NotePad is the default program to open/view CSV files.

 

[MNJ1193]

I never realized that, but it makes sense since Microsoft finds a way to screw up our lives in so many different ways.

This CSV sheet is for a website, so when I download it it to update the site. As long as I know that the upc codes are in the correct format once that sheet gets uploaed to the site for population, that is what matters.

Thank you for your help!

 

[Joe4]

You are welcome.

This CSV sheet is for a website, so when I download it it to update the site. As long as I know that the upc codes are in the correct format once that sheet gets uploaed to the site for population, that is what matters.

Yes, after you make your changes and save your CSV, if you then view your CSV file in NotePad, you can feel confident that what you see there is how the data actually resides in the CSV file.