Convert 0 value cells to "text"

[brucas]

I am trying to create a formula that will convert cells that are either blank
or displaying a 0 value to displaying "RDO" BUT if I enter a value other than
0 I want it to display that value.
I tried several different things but I am not having any success. Hopefully
someone with much more knowledge than me can help...

 

[fake_be]

Hi Brucas,

An IF formula looks to me the simpliest solution

Column A
0

text

Column B
=IF(A1=0;"RDO";IF(A1="";"RDO";A1))

Copy the formula until the end of the B column.

Does this do the trick?
Tom

 

[brucas]

Unfortunately not... It won't allow me to process it as it contains an error.
I have been trying to use various IF formulas to no avail.
The best I could get it to do default all the highlighted cells to RDO when
I copied them down. When I then enter other data it erases the formula ,hence
the RDO, but if I hit enter RDO remains (which is what I ultimately want to
happen). I may have to leave it that way however it makes the worksheet look
very busy and I prefer to be looking at a clean page.

HOLD ON... I altered the formula you gave me one last time and it has worked.

=IF(C3="0","RDO",IF(C3="","RDO",C3))

When I put the 0 into the "" it worked.

Thanks Heaps!! BUT now I have another one...

Does the IF Formula have to be in a seperate column? or can it be set to
"run in the background" on the column you are entering data?
I'll have a play and see as I'm determined to get this to work.

 

[brucas]

After some further investigation I've found that it does not work "in the
background" it defaults all cells to 0 and as you enter values it overwrites
or remains a 0 rather than RDO.

Also when I turned the show formulas off it had copied all the data from C
to D as well. GRRR!!

 

[fake_be]

Does the IF Formula have to be in a seperate column? or can it be set to
"run in the background" on the column you are entering data?
I'll have a play and see as I'm determined to get this to work.

If you want to use only one columm you should create a macro that
changes the value of the cells. Something like a find a replace
function. If so the 'original' data will be lost.

An other way of "detecting" would be via the 'conditonal formatting
function" of excel. Via Format, conditional formatting it is possible
to highlight some specific cells based on a value of formula.
Disavantage is that the colors are hard to use in other formula's....
(if neccessary)

Good luck

 

[David Biddulph]

Your amended formula will give RDO for a blank cell or for a text string of
"0", but not for a number of zero. The quote marks denote a text string.

Also, you don't need 2 IFs. An OR will do.
=IF(OR(C3=0,C3=""),"RDO",C3)

 

[David Biddulph]

And (apologies for following up my own post) as an empty cell is treated as
zero,
=IF(C3=0,"RDO",C3) will do.

 

[brucas]

Thanks Guys,
Your suggestions have actually helped me out a lot to understand what it is
that I am trying to do. What I had earlier is exactly the formula you have
below David.
As I was saying before though, I think that I will just have to leave it
showing the RDO on the worksheet as the default value instead of having white
spaces because if I use conditional formatting to make the RDO "invisible"
they will not show when I enter the rest of the data, hence I'm back where I
started this morning - Oh well, tomorrow is another day and the plight of a
perfectionist is never ending dissatisfaction...