PC Review


Reply
Thread Tools Rate Thread

copy and paste the cell with formula ="1"

 
 
HS Yong
Guest
Posts: n/a
 
      16th Jul 2009
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 With Quote
 
 
 
 
JoeU2004
Guest
Posts: n/a
 
      16th Jul 2009
"HS Yong" <HS (E-Mail Removed)> 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 (E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 With Quote
 
 
 
 
Jacob Skaria
Guest
Posts: n/a
 
      16th Jul 2009
To convert your data to numbers. Copy the range.Right click>Paste
Special>Values> 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 click>PasteSpecial>Add. 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!

 
Reply With Quote
 
HS Yong
Guest
Posts: n/a
 
      16th Jul 2009
Hi Jacob and JoeU,

Thanks for your advice!

Best Regards,
HS Yong

"JoeU2004" wrote:

> "HS Yong" <HS (E-Mail Removed)> 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 (E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell suyash.nathani@gmail.com Microsoft Excel Worksheet Functions 2 7th Nov 2007 10:39 AM
Copy and paste cell once! Cell deselects after paste. =?Utf-8?B?U3ByZWFkc2hlZXQgUmFiYml0?= Microsoft Excel Crashes 0 7th Dec 2005 07:37 PM
How do I get copy/paste to copy/paste text and not the whole page =?Utf-8?B?Q2Fyb2wgSi4=?= Microsoft Word Document Management 1 6th May 2005 09:03 PM
Copy and Paste macro needs to paste to a changing cell reference =?Utf-8?B?bG91bG91?= Microsoft Excel Programming 0 24th Feb 2005 11:29 AM
Copy & Paste Object without using the Excel Copy Paste functions =?Utf-8?B?R2Fueg==?= Microsoft Excel Misc 0 10th Mar 2004 08:06 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:26 AM.