Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a column of numbers that appear like this:

enter image description here

but the actual value of the shown cell is 20130.153334

Other values have a different number of decimal places. I don't want to add redundant 0's, so I can't just specify a particular number of decimal places to display. I really just want to treat the values as text.

I have already changed the format of the cell to Text, as the description for Text is: "Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered.". However, it clearly isn't being displayed exactly as entered! Strangely, if I hit F2 on the cell to go into edit mode, then hit enter, it is then displayed correctly. I can't do this manually for 2000+ records though!

How can I prevent the numbers being rounded?

share|improve this question
1  
What you've shown is correct because you are only displaying 5 decimal places. What happens if you change the number format to 6 decimal places? –  CharlieRB Feb 7 '12 at 21:07
    
If I do that for the entire column, I get redundant 0's on the end of other values that only have 5DP's –  Highly Irregular Feb 7 '12 at 22:08
    
Which excel is this? 2003? 2007? –  Jay Feb 7 '12 at 22:20
    
Excel 2007..... –  Highly Irregular Feb 7 '12 at 23:11

6 Answers 6

up vote 2 down vote accepted

If you already have a number in the cell then you can't change the format to text (...or you can but it doesn't work!). You can verify that - if data is in A1 then change format to text and use this formula in B1

=ISTEXT(A1)

The answer will be FALSE

You need to format the cells as text before you input the data - then the data will be displayed as entered

To format the column as text "after the fact" try this:

Select column of data then use

Data > Text to Columns > Next > Next > under "column data format" select "Text" > Finish

That should format the whole column as text and show the number of digits for each entry as entered.

share|improve this answer
    
I think you're onto what I want to do, except of course saying I can't do it doesn't really help a lot! In my case, I don't input the data myself, however extracting it and putting it back in might be a valid option. –  Highly Irregular Feb 7 '12 at 22:06
    
@Highly Irregular - Sorry, I was assuming the other answers would give you what you needed - I was simply trying to explain why your approach didn't work. See my revised answer above...... –  barry houdini Feb 7 '12 at 22:53
    
Thanks, @barry-houdini, great answer! That works nicely. –  Highly Irregular Feb 8 '12 at 1:05

Your problem is that you're formatting the numbers as text rather than number and you don't have enough decimal places showing so it has to round.
crtl + ASelect all - right click - format cells - number - change decimal places to however many you want to show and check use 1000 separator (,)

share|improve this answer
    
This would be fine if all the numbers had the same amount of decimal places (or if I was happy with redundant 0's on the end). Unfortunately, that's not the case. I essentially want the numbers treated like text and displayed exactly as they are stored. –  Highly Irregular Feb 7 '12 at 21:57

This site explains, how to set your precision from rounding.

short version: Ctrl-a to mark all cells, right click on a cell -> choose format cells -> select your precesion

share|improve this answer
    
See my comment on Raystafarian's answer. –  Highly Irregular Feb 7 '12 at 21:58

crtl + ASelect all - right click - format cells - custom- ########.##################### will do what you want
It won't show leading or trailing zeros

share|improve this answer
    
Thanks, @Raystafarian, that works nicely! This wasn't the only answer that worked, and after some thought I decided I prefer the other one (as it converts the data format to be text), but this one is perfect for anyone happy (or wanting) to retain a numeric data format. Hopefully some upvotes will bring it to 2nd place... –  Highly Irregular Feb 8 '12 at 1:13
    
Feedback that somebody attempted to edit in with an anonymous edit (that I accidentally accepted, and then promptly rolled back, oops): "The problem with this is, that it will show the decimal point even after numbers that have no decimal digits. if it's a whole number, it will show the whole number plus decimal point :(" –  nhinkle May 18 '12 at 1:05

As a person whom in a similar situation I've reached to following resolution. crtl + A Select all , crtl + C Copy all. Then in a new sheet Select all, Turn all cells into Text. You may use, "Quick Access Toolbar", "Home" Tab, "Number" Part . Switch to "Text" from "General". Then you may Paste all here first, to have the previous formatting , then again with Paste Values to have the values as they were. If this doesn't give satisfying results. Paste all into an empty Word document, (If you need any replacing do it here, for Word treats numbers as text) then again Select All and Copy All from that Word document. Before, pasting back turn all cells into text format. And finally, when pasting (into A1, with right click) use "Match Destination Formatting" option. I think this will do.

share|improve this answer

If 41200 ÷ 825 = 49.93939393, and you wish that the figure only 49.93 is not made round, then use =round(41200/825,4) and select format - cell - number decimal places select 2.

Your figure will be show 49.93 instead of 49.94 and be rounded off by the computer.

share|improve this answer
    
The question concerns displaying number data exactly as it is input, not truncating numbers without rounding them. –  Excellll Nov 16 '12 at 21:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.