0

What formula can I format my cells to so that when I input a number it will have a u after it (I have it set to ###,###u now) and if it is blank it will have "N/A" in the cell? Thank you in advance.

1
  • ###,###u works for me, what value are you trying to display?
    – parkydr
    Apr 8, 2013 at 14:07

2 Answers 2

0

What you require, assuming for A2, might be: =IF(ISBLANK(A2),"N/A",TEXT(A2,"###,###u")).

0

This cannot be done without VBA (which, of course, can do almost anything desired).

The "u" after a number is easy as you yourself, and the other answerer note. However, under no circumstance involving normal formatting or conditional formatting only will Excel trigger the formatting if the cell content is a blank.

So, for instance, you might set normal formatting to ###,###u;-###,###u;\N\/\A;@ expecting, since Excel often treats a blank as a 0, that the N/A in the zero value formatting place will get you your desire. But it will not as Excel sees the blank as different in this instance.

You might try something like ISBLANK(A1) as the formula inside a conditional formatting rule, but while it would trigger the rule to work on other cells, it will not trigger it to work on itself. I'd say "Go figure..." except it's actually understandable if you see it the right way: The cell you are acting up, say that's C1, is being formatted by the rule, not the actually blank cell. (I suspect that if both are blank and you have good or better VBA skills you could determine neither is affected. So, actually consistent with the idea.)

It's the actual blankness, not appearance of blankness, that is the root of the problem and since you desire the actual blankness, there's no way past it in Excel itself.

The only decent solution would be to place values in all the cells you wish to do this for. "" is the idea, not 7 or "horse", and assuming you don't want a wall of "N/A" values for someone to have to wander about in entering data (preferring a wall of empty looking cells for them to enter data into). Still, their random wrong cell entries followed by Delete's would still mess that up. As a practical thing, it's not overly doable.

But then again, somewhere between basic and good VBA skills and exact knowledge of your situation would surely solve it for you.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .