If cell has a currency with cents, show that value otherwise show the #

[Peptide]

Hello -looking for some help. Thanks in advance!


A1 = $12.55

B1 - If A1 has any cents (.01 - .99), show that full # or $12.55. If there are no cents (i.e. 12.00), then show only the $, not the cents or $12

Example 1
A1 = $12.55
B1 = $12.55

Example 2
A1 = $12.00
B1 = $12

 

[Eric W]

There are a few ways to do this. Assuming you have A1 and A2 formatted as currency, you can use this formula in A2:

=IF(INT(A1)<>A1,A1,TEXT(A1,"$#"))

The downside is that when it is an even dollar amount, what comes out is a text string, not a numeric value. But that might work for you.

Another way is to use Conditional formatting. Select A2, click Conditional Formatting > New Rule > Use a formula > and enter:

=INT(A1)=A1

click Format... > Number > Currency > and scroll the decimal places to 0. Or choose whatever format you want. Click OK. That will leave the value as a number, but display it as you desire.

 

[Peptide]

This works fantastic! Thanks so much!

 

[Eric W]

Happy to help!