I am trying to make an excel sheet show a trailing 0 after a decimal place if a value is rounded to a whole number (ie 39.0 is displayed when value is 38.9), however there is a caveat. It needs to show this in a cell formatted as TEXT and not a number.

I have tried making a NUMBER formatted cell do the calculation and rounding, formatting to add the trailing 0, then have the TEXT cell equal the calculation cell with the trailing 0 but it does not work and the TEXT cell just shows the formula making reference to the calculating cell. (ie the TEXT cell displays "=B4")

If I set the TEXT cell to GENERAL and enter the formula to refer to the calculating NUMBER cell I get the value of the calculation, but without the 0 on the rounded whole number (ie. 39). I can then change the cell to TEXT but the number stays the same.

I am joining this worksheet to a geodatabase in ESRI's Arcmap and for format/layout reasons the joining fields all have to be data type STRING, meaning I have to force all my joining excel cell formats to be TEXT so they become STRING when Arcmap looks at them.

Is there a way to have a TEXT cell show the exact displayed value of a NUMBER cell without the TEXT cell dropping the NUMBER cell's formatting?


Use the following, =TEXT(ROUND(C4/D4,1),"#,##0.0") where the Round function limits to one decimal place and the Text function includes formatting in the last argument. If you are dealing in currency, just add a $ sign like this, =TEXT(ROUND(C4/D4,1),"$#,##0.0") or add additional decimal points like this, =TEXT(ROUND(C4/D4,1),"#,##0.000").

If you know the functions you wish to use such as Text, you may type it into the help on Excel (use F1 or search help bar depending on the version of Excel you are using.

| improve this answer | |

I would like to suggest better use the Ceiling Function instead of the Round.


Where 1 is significance of number.

Because Ceiling is technically better and if you use both the Formula on 38.9, you find Round will reflect 38.9 but Ceiling give you 39. Even Roundup is also not producing 39.

Reason is simple Ceiling considers the whole number but Round and Roundup considers number after the decimal.

Now returning to your other issue that is, you need Trailing Zero after decimal. To solve the issue you need to select the Cells then click Number Format Tool Button then click Custom format and simply apply this $#,##0.00.

I'm not suggesting you to use Text command to format the cell because after that if any where you use that particular cell for calculation, you get #Value error.

Hope this help you I've posted the solution after Commands bee tested by me.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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