Thread: If cell has a currency with cents, show that value otherwise show the # Thanks: 0 Likes: 0

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

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

2. Re: If cell has a currency with cents, show that value otherwise show the #

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.

3. Re: If cell has a currency with cents, show that value otherwise show the #

This works fantastic! Thanks so much!

4. Re: If cell has a currency with cents, show that value otherwise show the #

Happy to help!