Ask Your Question

currency is not showing cents

asked 2017-05-02 21:49:50 +0200

BHM gravatar image

I have columns formatted to currency and I would like it to automatically add a decimal before the last two entered numbers. EX. If I enter 1234 I would like it to automatically show as $12.34 but instead it is showing as $1234.00. How do I change this?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-05-02 23:26:36 +0200

Lupp gravatar image

updated 2017-05-02 23:58:18 +0200

If you insist on entering money amounts based on the unit 'Cent' I can see two ways.

1) Dedicate a column to the entering of cent tallys, and convert the values to the unit $ by fourmulas in another column. Let the number of cents be in column A (example: row 2) a formula in B2 reading =A2/100 will do the conversion.

2) Also insisting on getting the amount shown in the unit $ in the cell where it was entered in the unit cent, you need to rely on user code:
a) Define a specific cell format and apply it to column A.
b) Write a Sub (in BASIC e.g.) as described below and assign it to the sheet event 'Content changed'.
c) Include a statement in the sub to exit it if the event was raised by a cell not having the specific format mentioned.
d) Otherwise let the sub divide the entered value by 100.


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-02 21:49:50 +0200

Seen: 59 times

Last updated: May 02 '17