Excel (2003) Format Cell help

rhysduck

rhysduck

rhysduck

Soldato
Joined
25 Oct 2009
Posts
6,706
Location
Caerphilly
Hi,
I have a number of cells with data like the following;

10,12,14
8,10,12
18.75,18.75,18.75

How do I create one custom format so the end result is this:

10mm, 12mm, 14mm
8mm ,10mm ,12mm
18.75mm ,18.75mm ,18.75mm

i can get the custom format to work for the first two but through the decimal point in and it hates it :(
 
Set all the numbers formats to 0"mm", then you can use conditional formatting with a custom formula like:

=INT(A1)<>A1

It means, if the cell is not an integer, return TRUE. Then set the number format when the condition is true to be #.##"mm".
 
Last edited:
The best solution I believe is to treat the cell as text, not a number. General"mm" will treat the cell as text and just add 'mm' to the end of it :)
 
Just use concat?

=CONCATENATE(A1,"mm")

Set the fields to text?

fnVPu.png


COL A is set to general... just works.
 
A[L]C;22475284 said:
Or set the number format to be 0.0"mm"
Click to expand...
Which doesn't work - results in "12.0mm" intead of "12mm"
james.miller said:
The best solution I believe is to treat the cell as text, not a number. General"mm" will treat the cell as text and just add 'mm' to the end of it :)
Click to expand...
Maybe, but then it starts setting the decimal places based on a combination of column width and the values entered. I think this is the better solution if you don't need to be precise, and depending on what the other values are.
rexehuk said:
Just use concat?

=CONCATENATE(A1,"mm")

Set the fields to text?

http://i.imgur.com/fnVPu.png[IMG]

COL A is set to general... just works.[/QUOTE]Another array of formulae to do what formatting can do is unnecessary, really.

I still think my solution is best, of course ;)
Click to expand...
 
You must log in or register to reply here.
Back
Top Bottom