copying blank cell

L

LA

COPYING CONTENTS OF A BLANK CELL USING A FUNCTION DOES NOT
RESULT IN A BLANK CELL BUT A CELL WITH A DATE.
 
T

Tom Ogilvy

=if(len(trim(A1))=0,"",A1)

But that only makes the cell look blank - it isn't blank because it contains
a formula. It won't contain a date unless you format it as a date. It would
contain zero if you used

=A1
 
J

J.E. McGimpsey

First - using all caps is considered shouting - please don't do that.

If you reference a blank cell, the value returned will be zero (for
numeric functions) or the null string.

A1: =J10

will display 0 if J10 is blank and A1 us formatted as General. Since
dates in XL are just integer offsets from a base date, 0 is
equivalent to 1/0/1900 (or 12/31/1899) in the 1900 system or
1/1/1904 in the 1904 system.

You can trap this condition using

A1: =IF(J10<>"", J10, "")

which returns the null string instead of zero.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top