[Mr.Guvernment]

Hey all!


was curious

i hae a simple excel spread sheet - how ever one colum contains 23 digit
numbers - but everytime i type them in - excel goes and turn the number into
some equation.... i do not want this, i simply want the cell to have he
numebr.

i have tried the various "foromating" of the cell, but they all make the
cell into an equation

Ex.

i type in

123456789653223


Excel then shows in the cell

1.23457E+14


how can i prevent this!


if i format the cell as a "number" with 0 decimals it chganges the last few
numbers of the cell.

[dergam]

The 1.23E14 you refer to is simply scientific notation (it rounds the number off to a [somewhat] significant amount of viewable digits based on available space and then multiplies the number by 10 [E] to the 14th power. It's simply a matter of formatting.

However, if you want it to show something (such as an order number or customer number) where this is not appropriate, simply change the cell formatting to the number style with zero decimal places or custom category type 0. Either will achieve the same result. Also, don't forget to make the cell wide enough to hold the 14 digits, or Excel will display as many pound signs (#) as it can.

Hope that helps.

[Mr.Guvernment]

K

well,

i tried formating the cell as a "number" with 0 decimals - still did it

then i did a custom formating and choose 0 - this time it put in the number, but it changes the last few digits to something other then what i pasted into the field.

1. pasted 1234567893214568 into a cell
- Excel turned it into 1.23457E+15


2. Formated cell with Custom - 0
- Excel made it 1234567893214560


when it should be

1234567893214568


Why does excel change the number to something else? it subracts 8....

[AndyFair]

There's two ways round this - format the cells as text (custom code @), or type an apostrophe (') before you type the number. Either way will get Excel to treat the number as a string and not as a number.

You may need to edit the numbers to get them to display properly (press F2, then just press Enter should be enough).

Rgds
AndyF

[Lotus]

Quote:

type an apostrophe (') before you type the number

Rgds
AndyF

Isn't it quotation marks? (") Well thats what I use anyway =) Seems to do the same thing. Not saying your wrong, just never done it that way =)

In the cell try "1234567890" see if that works for you.

Good luck

[Mr.Guvernment]

the @ sign does th trcik,

or a , after the number i was told elsewhere


now, i select a row of emtpy cells i plan to put these numebrs into and i choose format custom @

but when iput the number in Excel does its pain in the arse math equation crap again!

so i need to AGAIN format the one cell, then paste the number into it again!


i really do NOT like excel anymore!

[AndyFair]

Quote:

Isn't it quotation marks? (") Well thats what I use anyway =) Seems to do the same thing. Not saying your wrong, just never done it that way =)

In the cell try "1234567890" see if that works for you.

Good luck

No need to surround the figures with quotee marks, just precede them with an apostrophe.

Quote:

the @ sign does th trcik,

or a , after the number i was told elsewhere


now, i select a row of emtpy cells i plan to put these numebrs into and i choose format custom @

but when iput the number in Excel does its pain in the arse math equation crap again!

so i need to AGAIN format the one cell, then paste the number into it again!

i really do NOT like excel anymore!

The bummer with Excel is that when you copy and paste, it pastes the format as well as the data.
If your number is already displayed as "1.12345E17" or something, then also, unfortunately, Excel will have truncated the number already - which is why in the example you gave above when you changed the format, the number displatyed was different from what you enetered.

Looks like the only way you're going to get around this is format the cells first, then type the data in again...hope it wasn't too much ;(

Rgds
AndyF

[Mr.Guvernment]

i havent began typting yet, right now it is in a word doc


i tried formating the cells first, but did not try just typing, will try that as opposed to copy n pasting the number from the word doc.

[adamvjackson]

If you want the entire page's cells to have 'text' mode set, click on the box in the upper-left hand corner between the 'A' and '1' so that all cells are selected, then right-click and choose 'Format Cells'. From the 'Category' listing, choose 'Text' then click 'OK'.

All numbers entered in after that will be treated as text, not formulas. So you could have 3/20/03 (as an example of a date) and it would not try to do the division.

If you have further questions, let me know, and good luck.