Formula that generates a null value

[Hall]

Is there a function or value I can use in a cell formula that would put a
null value in the cell? For example, suppose my formula is

=if(A1=1,<null value>)

and suppose this condition (A1=1) is met. If I copy this cell to the
clipboard and then special-paste it to another cell for just "values", then
I want nothing (empty!) to be put in the target cell.

 

[Peo Sjoblom]

=IF(A1=1,"","do something else")

since you didn't say what you want if the condition is false

 

[Hall]

But "" is not empty. When I use that and then copy->special paste to
another cell for just values, the target cell gets a non-empty value.

How do I know its non-empty? Because if the cell to the left of it has a
text value longer than the column width, it would only show the entire text
of that cell if the cell to the right of it is indeed empty. Try it and
you'll see what I mean.

So this will not work. Any other ideas?

 

[Peo Sjoblom]

OK, I see what you mean. However since you paste in a null string it can't
be empty
Maybe I should ask: What is it that you are trying to do? It doesn't make
any sense to me to paste
a null string as values, why would you want to do that? Anyway, a formula
cannot return a truly empty value

 

[Hall]

Peo

I have a column of task descriptions (text) and I want adjacent 3 columns to
show those descriptions but based on another column's values per row. For
example, my column has this

description the first
description the second
description the third
description the forth
....


To the right of this column, I have 3 columns that would look something like
this (after a macro creates these formulas):

description the first
description the second
description the third
description the forth

As you can see, the indenting is based on applying the values based on
another column's values but the cells without the text must be empty (really
empty) so that I don't get this

description the
descripti
descripti
description the forth

 

[Peo Sjoblom]

Can't you have the macro autofit the width of the columns, one way would be
to set the width
of the columns to something you'll know will fit?

 

[Hall]

I want it to overlay. It should look like a slight indent.

 

[Dave Peterson]

How about using Peo's suggestion with a minor modification:

=IF(A1=1,na(),"do something else")

Then do your copy|paste special|values
then select that range and
Edit|replace
#n/a
with
(leave blank)
replace all.

You could use any unique value (not just na()) and get the same results.

====
If you already have a bunch of those "non-empty" blank cells, you could do this:

Ctrl-A (to select everything--or just select the range you want)
Edit|replace
(leave blank)
with
$$$$$$ (some unique string)
replace all.

And now do the reverse
Edit|replace
$$$$$$ (the same unique string)
with
(leave blank)
replace all.

Those problem cells will now be really empty.

===
If I think of it beforehand, I do the na() bit. If I don't, I have to do more
steps!