[wreakhavoc]

Why does something different frequently come up in the cell than what I typed in?

[puzzlehead]

before/after screenshot? What are you trying to type? What comes up? My first guess would be cell formatting.

[wreakhavoc]

before/after screenshot? What are you trying to type? What comes up? My first guess would be cell formatting.

Next time it happens I'll get a screenshot.
I had typed a long number like 130173093452 and it came out something like "13.17393+11" If I type in 4/21 it comes up as "21 Apr" If I type in a dollar/cents amount ending in zero such as 14.20 it comes up as "14.2"

[puzzlehead]

Right click the cell (or column) and click 'Format Cells'. You should be able to fix it easily. When I have time I'll post screenshots (I'm using multiple cellphones today to cruise the web).

[J-Mah]

http://www.functionx.com/excel/Lesson05.htm

[ScorpionSoul]

If you want a number you type to show up exactly as you type it, especially in the case of a zero after a decimal point (14.20), or a number starting with zero (012345), you need to format that cell as "text", so it will leave what you type as you type it.

Regarding 4/21, Excel is recognizing it as a date, which is why it changed it to 21 Apr. Change it to text, or if it IS a date you're typing, just not in the format you want, choose the appropriate date format for that cell.

[wreakhavoc]

Thanks all. I see I have a lot to learn about it. Why did the long number that I typed in (in a column that I had always typed in long numbers) came out with a decimal point in it and the "+11" at the end? This was just a one time occurrence, I had never changed any settings.

This post has been edited by wreakhavoc: Nov 23 2007, 06:34 AM

[Smash]

Thanks all. I see I have a lot to learn about it. Why did the long number that I typed in (in a column that I had always typed in long numbers) came out with a decimal point in it and the "+11" at the end? This was just a one time occurrence, I had never changed any settings.

it displays that way because you exceeded the width of the column. if you click on the header of that column between it and the next column, it will auto expand to the widest necessary size for a value in that column.

Otherwise you can format it to display a certain number of decimals only, by editing the format of the column.

[doa12]

Thanks all. I see I have a lot to learn about it. Why did the long number that I typed in (in a column that I had always typed in long numbers) came out with a decimal point in it and the "+11" at the end? This was just a one time occurrence, I had never changed any settings.

it displays that way because you exceeded the width of the column. if you click on the header of that column between it and the next column, it will auto expand to the widest necessary size for a value in that column.

Otherwise you can format it to display a certain number of decimals only, by editing the format of the column.

What he said. The +11 is scientific notation, saying that the decimal point needs moved 11 places. Scientific notation is 1 whole number, followed by all the digits, followed by the e+11.

So 1.87e+10 is 18,700,000,000. 1.87e-10 is .000000000187

By using the format, you can specify where the . goes and how many positions to display to the right of it.

Also, if your region is off, it could switch the . and the , as other countries can call a thousand bucks $1.000,00 rather than our method of $1,000.00

[Smash]

Also, if your region is off, it could switch the . and the , as other countries can call a thousand bucks $1.000,00 rather than our method of $1,000.00

Goddamn foreigners...

[nun]

If you want a number you type to show up exactly as you type it, especially in the case of a zero after a decimal point (14.20), or a number starting with zero (012345), you need to format that cell as "text", so it will leave what you type as you type it.

No, you format the number appropriately by specifying it as currency or defining decimal places using the format. Please don't ever advocate formatting numbers by using text. Especially considering we're in a spreadsheet.

[ScorpionSoul]

No, you format the number appropriately by specifying it as currency or defining decimal places using the format. Please don't ever advocate formatting numbers by using text. Especially considering we're in a spreadsheet.

Depends on what you're using the spreadsheet for. If you're using it for the mathematical properties, then yeah... don't format as text. But if the math isn't important, then it doesn't really matter.

We use excel for several forms at work, in which work centers are listed (010, 020, 030, etc...). It's much easier to just format the cells as text, then to click the little blue box every time it changes something and tell it to change it back to what you typed.

[~vjay~]

No, you format the number appropriately by specifying it as currency or defining decimal places using the format. Please don't ever advocate formatting numbers by using text. Especially considering we're in a spreadsheet.

Depends on what you're using the spreadsheet for. If you're using it for the mathematical properties, then yeah... don't format as text. But if the math isn't important, then it doesn't really matter.

We use excel for several forms at work, in which work centers are listed (010, 020, 030, etc...). It's much easier to just format the cells as text, then to click the little blue box every time it changes something and tell it to change it back to what you typed.

The general rule would be to format to text like you said if the intent is to not perform mathematical equations on your cells but to instead keep the format without anything ever changing in it. Numbers could be used for the same thing but at least you can chose to select the decimal format if needed. Obviously currency is money. I'm yet to use the Accounting format though.

I noticed out of curiosity that when I did an equation in a cell which added 2 cells I got a weird number/letter combo of 1.32E+08 which was due to the cell needing to be resized to accommodate the number.(which is what Smash said)

[nun]

We use excel for several forms at work, in which work centers are listed (010, 020, 030, etc...). It's much easier to just format the cells as text, then to click the little blue box every time it changes something and tell it to change it back to what you typed.

Those are text labels, not numbers that you would possibly ever want to perform operations on as any such operation would be meaningless.

1.32E+08 = 132,000,000 in Scientific Notation



This post has been edited by nun: Nov 26 2007, 01:25 AM

[ScorpionSoul]

Right, which is why I mentioned not to format as text if you need the mathematical functions.

We're on the same page, sort of, just reading from different directions. (IMG:style_emoticons/default/biggrin.gif)

[nun]

Right, which is why I mentioned not to format as text if you need the mathematical functions.

We're on the same page, sort of, just reading from different directions. (IMG:style_emoticons/default/biggrin.gif)

Nah, I'm just a lot more technical and anal about data.

Just because you don't expect to sum up that $11.50 today, doesn't make it right to funge it with text. It takes just about the same effort to format it as currency as it does text.

Same goes for dates. If you ever want to sort by date later on 11/7 will sort before 4/20. If you format as a date, you'll also get the benefit of a default of the current year that will be in the field even if it doesn't show up in the formatting.

Room numbers and address numbers are text. No problems there.