CODEWITHSUNDEEP
excel
user234194
user234194

Reputation: 1723

excel conditional formatting help

I have formatted the columns as follows:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??);(@_).

If I copy paste the value then it doesn't work. But if I type 1234, then it automatically converts to $ 1,234.

But how can I make it work even I do pasting.

I googled it and found that conditional formatting is used in these scenario.(Am I right?)

I tried doing conditional formatting also but could not secceed.

Can somebody tell me how to achieve this in conditional formatting:

  1. How to convert text so that final value will be like $ 1,234 ?
  2. If the column is empty or white space , replace it with - in the center.

Any type of help is appreciated.

Thanks.

Upvotes: 0

Views: 810

Answers (2)

pnuts
pnuts

Reputation: 59495

If what appear to be numbers are imported as text there is likely to be a warning of this in Excel (a small green triangle in the top left of the cells - for all recent Excel versions can be turn on/off in Options, Formulas, Error Checking). By selecting a range that includes the first instance, these strings can be converted to General format (so that values are recognised as numbers) by clicking the warning icon and Convert to Number.

Then 'standard formatting' can be applied, perhaps: 

` $* #,##0 ;($* #,##0)`

and empty cells selected with HOME > Editing - Find & Select, Go To Special..., Blanks then formatted Alignment Center and contents entered with Find (nothing) and filled with hyphens with Replace with -.

Upvotes: 0

Stewbob
Stewbob

Reputation: 16899

When you paste something into a cell in Excel, by default, the format is also pasted. To prevent this, you must do a Paste Special, then choose to only paste the Value.

For the conditional formatting of the currency, you can set up a rule that looks like this:

enter image description here

Getting a '-' to appear in a blank cell is not possible in Excel formatting rules. A dash can be inserted as formatting, but only as long as there is actual data in the cell. If the cell is blank, Excel will not display any characters.

Upvotes: 3

Related Questions