Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and paste the cell with formula ="1"
When copy cell A1 contain ="1" and Paste Special (Value only) to cell B1,
cell B1 value= 1. Then i type 1 at cell A2. I compare A2 with B1 and it show that B1 not equal with A2, but B1 equal to A1. But if i double click on the cell B1 and enter or click "convert to number". The result will be reverse. May i know why? A1 {="1"} ; B1 {copy cell A1 contain ="1" and Paste Special (Value only) to cell B1} C1 {=if(A1=B1,"Yes","No")} A2{1}; B2{blank}; C2 {=if(A2=B1,"Yes", "No")} Initial result: C1= Yes, C2=No after double click on the cell B1 and enter or click "convert to number": C1= No, C2=Yes I have lots of cells, some with formula {="xxx"} and some without formula {xxx}. How to convert all to the numbers by few steps? Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and paste the cell with formula ="1"
To convert your data to numbers. Copy the range.Right clickPaste
SpecialValues OK. Still if you have any issues with the numbers --Copy a blank unused cell. Select the range of cells you want to convert. Right clickPasteSpecialAdd. click OK If this post helps click Yes --------------- Jacob Skaria "HS Yong" wrote: When copy cell A1 contain ="1" and Paste Special (Value only) to cell B1, cell B1 value= 1. Then i type 1 at cell A2. I compare A2 with B1 and it show that B1 not equal with A2, but B1 equal to A1. But if i double click on the cell B1 and enter or click "convert to number". The result will be reverse. May i know why? A1 {="1"} ; B1 {copy cell A1 contain ="1" and Paste Special (Value only) to cell B1} C1 {=if(A1=B1,"Yes","No")} A2{1}; B2{blank}; C2 {=if(A2=B1,"Yes", "No")} Initial result: C1= Yes, C2=No after double click on the cell B1 and enter or click "convert to number": C1= No, C2=Yes I have lots of cells, some with formula {="xxx"} and some without formula {xxx}. How to convert all to the numbers by few steps? Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and paste the cell with formula ="1"
Hi Jacob and JoeU,
Thanks for your advice! Best Regards, HS Yong "JoeU2004" wrote: "HS Yong" <HS wrote: When copy cell A1 contain ="1" and Paste Special (Value only) to cell B1, cell B1 value= 1. Then i type 1 at cell A2. I compare A2 with B1 and it show that B1 not equal with A2, but B1 equal to A1. [....] May i know why? In C1, enter =TYPE(B1); and in C2, enter =TYPE(A2). That should give you a clue. Answer: B1 is text (type 2), just as if you had typed "hi" without quotes. A1 is also text because you enclosed it in quotes. So A1 and B1 are equal because they are the same text string. On the other hand, A2 is numeric (type 1). Text "1" is not equal to the number 1. This might be surprising because in some contexts, Excel does treat numeric text strings as numbers. For example, =A1+B1 is the numeric value 2 (type 1). What can I say? No one ever accused Excel of being consistent :-). (Historical note: The fault probably lies with Visicalc, Lotus or Multiplan. Excel might simply be trying to be compatible.) I have lots of cells, some with formula {="xxx"} and some without formula {xxx}. How to convert all to the numbers by few steps? Enter 1 into some cell, say C1. Copy C1. Hold down the Ctrl key and click each cell that you wish to convert. Then right-click the last cell and click Paste Special, Multiply, OK. You can now delete the value in C1. That will effect the conversion. However, cells that were originally ="xxx" will now be =("xxx")*1. If wish to clean those up, do the following additional steps. Click on the cell and copy it. Then right-click the same cell and click Paste Special, Value, OK. You will have to do this with each cell one by one unless you can select a contiguous range that includes multiple cells to be cleaned up. At least Excel 2003 will not permit you to copy discontiguous cells that you select by holding the Ctrl key while clicking. ----- original message ----- "HS Yong" <HS wrote in message ... When copy cell A1 contain ="1" and Paste Special (Value only) to cell B1, cell B1 value= 1. Then i type 1 at cell A2. I compare A2 with B1 and it show that B1 not equal with A2, but B1 equal to A1. But if i double click on the cell B1 and enter or click "convert to number". The result will be reverse. May i know why? A1 {="1"} ; B1 {copy cell A1 contain ="1" and Paste Special (Value only) to cell B1} C1 {=if(A1=B1,"Yes","No")} A2{1}; B2{blank}; C2 {=if(A2=B1,"Yes", "No")} Initial result: C1= Yes, C2=No after double click on the cell B1 and enter or click "convert to number": C1= No, C2=Yes I have lots of cells, some with formula {="xxx"} and some without formula {xxx}. How to convert all to the numbers by few steps? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
copy--paste--special "formula" for a whole column | Excel Discussion (Misc queries) | |||
problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |