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

Peptide

Board Regular
Joined
Apr 12, 2016
Messages
220
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,811
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.
 

Forum statistics

Threads
1,078,450
Messages
5,340,360
Members
399,371
Latest member
wilbot

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top