custom number format

  • Thread starter Thread starter goepf
  • Start date Start date
G

goepf

I'd like to format a number in a cell that:

if the number has decimals, show 2
if the number has no decimals show none

2.23242 -> 2.23

14.0 -> 14

I tried ###.## and this gives a nice result except the point fo
integer values (14.0 becomes 14.)

any ideas how to get rid of the point?

Thanks
 
Hi goepf!

Sorry! But I don't think it's possible with custom formatting to get rid of
that point and that ###.## is about the best you'll get.

You should be able to achieve it with a worksheet_change event handling
subroutine. Or you could use a subroutine to retrospectively format as you
require. Post back if need assistance with either of those approaches.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
goepf,

One possibility: Insert a helper column. If the original numbers were in
column A, it might look like:

=ROUND(A2, 2) (copied down)

Now format the helper column General (it probably already is), and you can
hide the original column if you want to.

Note that the decimal (or implied decimal) points will not line up with this
solution.
 
Back
Top