Help with Excel problem/glitch?

[Monkeyleg]

I'm trying to put some part numbers into a column in Excel. It doesn't matter whether I use my existing table or start with a fresh one, what happens is always the same. I type in 719307300019 or 719307305253 or 719307304482 and Excel puts 7.19E+11 in each cell, no matter what the last four numbers of the part number string were.

I've looked everywhere in the tools to see if there was a macro that might be doing this, but I can't find one.

Any ideas?

[Hawkmoon]

Hmmm ...

Mine does that, too, with your numbers. Here's what seems to be going on:

Notes

•A number format does not affect the actual cell value that Microsoft Excel uses to perform calculations. The actual value is displayed in the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).
•The maximum limit for number precision is 15 digits, so the actual value that is displayed in the formula bar may change for large numbers (more than 15 digits).
•To reset the number format, click General in the Category list. Cells that are formatted with the General format have no specific number format. However, the General format does use exponential notation for large numbers (12 or more digits). To remove the exponential notation from large numbers, you can apply a different number format, such as Number.

Pay attention to the third bullet.

[Monkeyleg]

Thanks, Hawkmoon. That did it. I've never run into that before.

[Hawkmoon]

Nor had I.

The day is only an hour old for me and I learned something already. I guess I can go on autopilot for the next 23 ...

[Monkeyleg]

Well, it looked like the problem was solved, but it wasn't. When I opened the .txt file again, the part numbers were showing as 7.19E+11 again.

I tried all different formats, and nothing worked.

Anyone else have an idea?

[Doggy Daddy]

Try formatting it as text.  It shouldn't give you any problems unless you're trying to perform math functions on it.

DD


eta, if you do any sorting, Excel should pop up a box asking if you want to treat that string as if it was a number.  Say "yes" and all will go as you want.

[wmenorr67]

What DD said.

[Monkeyleg]

OK, I did more reading on this. If the column is formatted as text, and the file is saved as a delimited text file, the numbers are intact. I can open the file in Notepad, and the numbers are fine. It's re-opening the file in Excel that messes up the numbers, and shows them with a period, letter and +11.

So, I guess it works, but in a very bizarre way.

[Ben]

Try format cells/custom/0 and see if that helps.

[K Frame]

"So, I guess it works, but in a very bizarre way."

No, it works in a very logical way.

When you save it as a text file, native formatting features in Excel don't always convert over to the far more limited text file. In order to save the file as text, those non compatible formats have to be discarded.

When you open the stripped text file in Excel, of course it's going to show the x10 to the X number again because the formatting that kept it as a nice, neat, linear number is gone.

Save it as an Excel file when you're working on it, not as a text file.

[Doggy Daddy]

Try formatting it as text.  It shouldn't give you any problems unless you're trying to perform math functions on it.

DD


eta, if you do any sorting, Excel should pop up a box asking if you want to treat that string as if it was a number.  Say "yes" and all will go as you want.

As much as I hate to quote myself, let me repeat that formatting as text will fix the problem. Just be sure to format the cell as text before you enter the number into it.

[Monkeyleg]

Thanks again for the advice. I formatted the cells as text, entered the numbers, and when I opened it again, it had reverted to the 7.19E+11 value in the cell. I thought nothing of it until I clicked on the cells, and found that they all were the same number: 719307000000.

I won't be changing products in this table very often. I'll change prices from time to time, but that's it. I copied the numbers in order into a Notepad file. If I open the .txt file and the numbers are screwed up, I'll just delete them and paste the numbers from the Notepad file. It's a hack, but it's faster than trying to figure out why.

Mike, I also saved a copy as an Excel file so I can just copy the column if need be.

Thanks again.

[MrsSmith]

You can also try putting an apostrophe ' at the start of the string of numbers. That's supposed to keep the cell from auto-formatting in any way.

[Doggy Daddy]

You can also try putting an apostrophe ' at the start of the string of numbers. That's supposed to keep the cell from auto-formatting in any way.

This!

I use that sometimes when I'm copying a complex formula.  It keeps the formulae from changing when using relational cell references.

Thanks again for the advice. I formatted the cells as text, entered the numbers, and when I opened it again, it had reverted to the 7.19E+11 value in the cell. I thought nothing of it until I clicked on the cells, and found that they all were the same number: 719307000000.

Sorry Dick.  (That's an apology... not name-calling.)

DD

[Monkeyleg]

Excel is putting an apostrophe at the beginning of the sequence of numbers, or at least it's supposed to. I don't think Google would accept an apostrophe, though.

[Doggy Daddy]

What manufacturer are these part numbers for?  Trijicon?  I think if you could embed a Bible verse into the part number, Excel might leave it alone.   

DD

[Hawkmoon]

Thanks again for the advice. I formatted the cells as text, entered the numbers, and when I opened it again, it had reverted to the 7.19E+11 value in the cell. I thought nothing of it until I clicked on the cells, and found that they all were the same number: 719307000000.

I won't be changing products in this table very often. I'll change prices from time to time, but that's it. I copied the numbers in order into a Notepad file. If I open the .txt file and the numbers are screwed up, I'll just delete them and paste the numbers from the Notepad file. It's a hack, but it's faster than trying to figure out why.

Mike, I also saved a copy as an Excel file so I can just copy the column if need be.

Thanks again.

The problem is that when you save as a comma delimited text file, you are not "saving" the file, you are exporting the file. When you try to reopen it in Excel, you are not opening it, you are importing it into a new spreadsheet. Since a .TXT file carries no formatting, all cells in the new spreadsheet are back to the default "General" format.

Do your import, then just reformat the affected cells from 'General" to "Number" with no decimal places (or to "Text") and you should be good to go. I tested it reformatting the imported spreadsheet to "Number" and it worked.