PDA

View Full Version : [SOLVED] Custom Formatting



Aussiebear
07-13-2016, 10:49 PM
I've been entering data using a custom cell format ##.#"km", which allows the user to simply enter numerals on the run. Whilst this works for numbers greater than 1, it fails for numbers less than one.

Example Entering "23.5" shows as "23.5km", yet "0.9" shows as " .km".

Why does this occur and how do I get around this?

mancubus
07-13-2016, 11:20 PM
#0.0km

how about this?

mdmackillop
07-14-2016, 12:15 AM
Hi Ted

#,##0.0"km"

Aussiebear
07-14-2016, 01:22 AM
So, the pound sign is the limiting factor, as in integer not less than 1?

My thanks to both of you for this assistance.

Aflatoon
07-14-2016, 02:35 AM
With the format you posted, 0.9 should appear as ".9km" and not ".km"

Paul_Hossler
07-14-2016, 11:19 AM
So, the pound sign is the limiting factor, as in integer not less than 1?

My thanks to both of you for this assistance.

The '#' does not show "insignificant zeros" and the '0' does show "significant" zeros (MS help)

e.g. "$#,##0.00_)" will show $1,234,567.00, or $0.00, or $123.45, each with enough space for a ")" (to align with a ($1.00) formatted negative numbers)



There a number of other useful markers:

http://www.exceltactics.com/definitive-guide-custom-number-formats-excel/

mdmackillop
07-15-2016, 02:16 AM
You can also use # after the decimal
#,##0.0#"km" with show 3.2km (not 3.20km) if 3.2 is entered and show 3.25km if 3.25, 3.248, 3.251 etc is entered

Aussiebear
07-15-2016, 07:46 PM
Paul, great link provided.