Custom Formatting £1.50 to show as £1.50 but £1.00 to show as £1

[thequiff]

I am trying to find a custom format to show £1.00 as £1 and £1.50 to show as £1.50.

Many Thanks
Matt

 

[Teeroy]

You could probably modify Marcelo Branco's approach in http://www.mrexcel.com/forum/excel-questions/510333-custom-format-if-number-integer-dont-show-decimals-but.html to show 2 decimal places instead of one and check for ".00" on the end of the number.

 

[thequiff]

You could probably modify Marcelo Branco's approach in http://www.mrexcel.com/forum/excel-...if-number-integer-dont-show-decimals-but.html to show 2 decimal places instead of one and check for ".00" on the end of the number.

I've tried this, but I can't get it to effect it. Im using mac office 2011? Is it yet another thing that doesn't work on mac?

This is not beautiful but i think it works

Assuming formatting A1

1. First format A1 as Number with 1 decimal place

2. Go to Conditional Formatting
New Rule
Use a formula to.. (the last option)

and insert this formula
=SEARCH(".0",TEXT(A1,"0.0"))

Click the Format button
and pick Number with 0 decimal places

Ok, Ok

done

Now you can use the Format Painter to apply the format to other cells.

HTH

M.

 

[thequiff]

Actually it could be working? As I can get it to change red. But What I can't do is select format with then select numbers? I only get the option of Font, Border, and Fill?

 

[Teeroy]

Sorry I can't answer the Mac question (Apple free zone!) but on Excel 2010 for Windows you get 5 options under "Format Cells"; Number, Alignment, Font, Border, Fill and Protection.