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?
Best Answer
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.