Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting data from a website changes text to date | Excel Discussion (Misc queries) | |||
Large amount of text in cells - Problem text.xls (0/1) | Excel Discussion (Misc queries) | |||
Text Format problem | Excel Discussion (Misc queries) | |||
a problem with pasting into Excell sheet | Setting up and Configuration of Excel | |||
Problem with wrap text format | Excel Worksheet Functions |