problem when pasting text
 

set up some rows and columns thusly...

A B C D
format as general
A B A OR B
3 0 0 FALSE OR(B3="1",C3="1")
4 0 1 FALSE OR(B4="1",C4="1")
5 1 0 FALSE OR(B5="1",C5="1")
6 1 1 FALSE OR(B6="1",C6="1")

format as text
A B A OR B
10 0 0 FALSE OR(B10="1",C10="1")
11 0 1 FALSE OR(B11="1",C11="1")
12 1 0 FALSE OR(B12="1",C12="1")
13 1 1 FALSE OR(B13="1",C13="1")

at the right is the formula used in the column marked 'A OR B'.
first format the entire page as 'general'
then format the lower group of 0's and 1's as 'text'
the result sometimes works.

(yes, I could have used the more straighforward OR(Bx,cx), but this is
simply an example. my actual spreadsheet has a much more complicated
function.)

copy the 0's and 1's from the upper group and do a paste or paste special
value over the top of the lower group.
the formula results all change to FALSE which is WRONG!

why does Excel not preserve the formatting of the cells when pasting?

but if I type the lower group of 0's and 1's in by hand, the formulas work
okay.
I can't type in 4000 lines of data by hand!

I believe Excel does copy cell formats. When you use simple copy and paste,
it copies all formats. When you use copy paste special values, it copies only
values without formats. I believe the problem is that your formula is set to
read text values (i.e. "0" "1"). This is why it does not work. Your top area
is formatted as general so it reads the values as numbers. Your bottom area
is formatted to text, so when you use simple copy and paste, Excel copies the
general format and reads the values as numbers--your formula can't dectect
these numbers. To make matters worse, when you use copy paste special values,
Excel does not enter the numbers as text, it enters it as numbers. The only
way for Excel to read the numbers as text is if the top portion is formatted
as text, or to manually type them in.

I suggest two options.

First, format both top and bottom areas as either number or text. Then
modify your formula accordingly (i.e. if number, then 0 1; if text, then "0").

Or use the following formula: =OR(OR(B3="1",C3="1"),OR(B3=1,C3=1)). This
way, regardless of how you format your areas, the result will always be
accurate.

I hope this helps.
--
Thanks,

JB


"ew_jammer" wrote:

set up some rows and columns thusly...

A B C D
format as general
A B A OR B
3 0 0 FALSE OR(B3="1",C3="1")
4 0 1 FALSE OR(B4="1",C4="1")
5 1 0 FALSE OR(B5="1",C5="1")
6 1 1 FALSE OR(B6="1",C6="1")

format as text
A B A OR B
10 0 0 FALSE OR(B10="1",C10="1")
11 0 1 FALSE OR(B11="1",C11="1")
12 1 0 FALSE OR(B12="1",C12="1")
13 1 1 FALSE OR(B13="1",C13="1")

at the right is the formula used in the column marked 'A OR B'.
first format the entire page as 'general'
then format the lower group of 0's and 1's as 'text'
the result sometimes works.

(yes, I could have used the more straighforward OR(Bx,cx), but this is
simply an example. my actual spreadsheet has a much more complicated
function.)

copy the 0's and 1's from the upper group and do a paste or paste special
value over the top of the lower group.
the formula results all change to FALSE which is WRONG!

why does Excel not preserve the formatting of the cells when pasting?

but if I type the lower group of 0's and 1's in by hand, the formulas work
okay.
I can't type in 4000 lines of data by hand!

That works, but almost.
Try this and tell me if this is not a bug...

Paste the 0s and 1s from the general formatted are to the text formatted area.
Use normal paste.
The results will be incorrect since the cells are formatted as general.
Then select the lower area and change the cell format to text.
Shouldn't that make the formula work?
It doesn't.
I think Excel is reporting to me one format when it's actually another.
The double OR is a fix, but I still think Excel has a problem. One of many!
Thanks for the help.


"J. Banuelos" wrote:

I believe Excel does copy cell formats. When you use simple copy and paste,
it copies all formats. When you use copy paste special values, it copies only
values without formats. I believe the problem is that your formula is set to
read text values (i.e. "0" "1"). This is why it does not work. Your top area
is formatted as general so it reads the values as numbers. Your bottom area
is formatted to text, so when you use simple copy and paste, Excel copies the
general format and reads the values as numbers--your formula can't dectect
these numbers. To make matters worse, when you use copy paste special values,
Excel does not enter the numbers as text, it enters it as numbers. The only
way for Excel to read the numbers as text is if the top portion is formatted
as text, or to manually type them in.

I suggest two options.

First, format both top and bottom areas as either number or text. Then
modify your formula accordingly (i.e. if number, then 0 1; if text, then "0").

Or use the following formula: =OR(OR(B3="1",C3="1"),OR(B3=1,C3=1)). This
way, regardless of how you format your areas, the result will always be
accurate.

I hope this helps.
--
Thanks,

JB


"ew_jammer" wrote:

set up some rows and columns thusly...

A B C D
format as general
A B A OR B
3 0 0 FALSE OR(B3="1",C3="1")
4 0 1 FALSE OR(B4="1",C4="1")
5 1 0 FALSE OR(B5="1",C5="1")
6 1 1 FALSE OR(B6="1",C6="1")

format as text
A B A OR B
10 0 0 FALSE OR(B10="1",C10="1")
11 0 1 FALSE OR(B11="1",C11="1")
12 1 0 FALSE OR(B12="1",C12="1")
13 1 1 FALSE OR(B13="1",C13="1")

at the right is the formula used in the column marked 'A OR B'.
first format the entire page as 'general'
then format the lower group of 0's and 1's as 'text'
the result sometimes works.

(yes, I could have used the more straighforward OR(Bx,cx), but this is
simply an example. my actual spreadsheet has a much more complicated
function.)

copy the 0's and 1's from the upper group and do a paste or paste special
value over the top of the lower group.
the formula results all change to FALSE which is WRONG!

why does Excel not preserve the formatting of the cells when pasting?

but if I type the lower group of 0's and 1's in by hand, the formulas work
okay.
I can't type in 4000 lines of data by hand!