8

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?

3
  • 2
    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, 2012 at 21:07
  • 2
    If I do that for the entire column, I get redundant 0's on the end of other values that only have 5DP's Feb 7, 2012 at 22:08
  • 1
    Which excel is this? 2003? 2007?
    – Jay
    Feb 7, 2012 at 22:20

8 Answers 8

4

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.

3
  • 1
    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. Feb 7, 2012 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...... Feb 7, 2012 at 22:53
  • Thanks, @barry-houdini, great answer! That works nicely. Feb 8, 2012 at 1:05
7

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

2
  • 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... Feb 8, 2012 at 1:13
  • 1
    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, 2012 at 1:05
2

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 (,)

1
  • 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. Feb 7, 2012 at 21:57
1

If you want 49.93 instead of 49.94, one way to guarantee it is with a formula like:

=INT((41200/825*100))/100

What happens here is the decimal in your result is moved two places to the right, then INT strips off all decimals giving you 4,993, then it's divided by 100 to give you 49.93000, and you can format it to 2 places to get 49.93.

When doing this be careful with your parentheses.

1
  • To help others with this one, replace 41200/825 with the cell (A1 or whatever it is), or some other outputting formula.
    – user287352
    Feb 21, 2019 at 6:10
0

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

1
  • See my comment on Raystafarian's answer. Feb 7, 2012 at 21:58
0

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.

0

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.

1
  • 1
    The question concerns displaying number data exactly as it is input, not truncating numbers without rounding them.
    – Excellll
    Nov 16, 2012 at 21:55
0

I found this online and it worked for me very well - You can frequently prevent floating point rounding errors from affecting your work by setting the Precision as displayed option before you apply a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.

Note: Using the Precision as displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.

1.Click File > Options.

In Excel 2007: Click the Microsoft Office Button Office button image , and then click Excel Options.

2.Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.

3.Click OK.

4.In the worksheet, select the cells that you want to format.

5.On the Home tab, click the Dialog Box Launcher Button image next to Number.

Excel Ribbon Image

6.In the Category box, click Number.

7.In the Decimal places box, enter the number of decimal places that you want to display.

Tip: To minimize any effects of floating point arithmetic storage inaccuracy, you can also use the ROUND function to round numbers to the number of decimal places that is required by your calculation.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .