Excel – How to prevent Excel rounding numbers or adding redundant 0’s

microsoft exceltext formattingworksheet-function

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?

Best Answer

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.

Related Question