software

I've been using excel for quite sometime and there has been one thing that
seriously irks me. Say, for example, I have a field (or for my purpose I
have a column of 20000 fields) and the data as it is now is in text format.
A field for example is quot;29.665quot; (w/o quotes of course). If I choose to copy
this field into a new field it will bring it over as text or whatever format
the cell is originally will transfer over. This is fine and understandable,
however, if I change the field format to numeric with 2 decimals for
instance, the field is still in its original format unless I double click on
it, or click and hit F2 etc. So imagine doing this with 20000 fields.

Obviously having the data in the correct format to begin with is helpful
but sometimes you have to work with what you get. A workaround is to simply
copy the data - paste into a text file and then bring it back to a
spreadsheet where the destination fields aren't set as text. I strongly
believe that there should be some sort of quot;executequot; function after changing
cell formats to actually initiate the change since it doesn't occur
automatically.

Sorry for writing so much. Thanks in advance to any advice.

Click in an unused *new* cell and format it to number with 2 decimals.
Right click in this cell and choose quot;Copyquot;.

Select your 2000 row column.
Right click in the selection and choose quot;Paste Specialquot;.
Click on quot;Addquot;, then lt;OKgt;, then lt;Escgt;.

You now have *all* your text numbers as real numbers.--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

quot;sjrkuquot; gt; wrote in message
...
gt; I've been using excel for quite sometime and there has been one thing that
gt; seriously irks me. Say, for example, I have a field (or for my purpose I
gt; have a column of 20000 fields) and the data as it is now is in text
gt; format.
gt; A field for example is quot;29.665quot; (w/o quotes of course). If I choose to
gt; copy
gt; this field into a new field it will bring it over as text or whatever
gt; format
gt; the cell is originally will transfer over. This is fine and
gt; understandable,
gt; however, if I change the field format to numeric with 2 decimals for
gt; instance, the field is still in its original format unless I double click
gt; on
gt; it, or click and hit F2 etc. So imagine doing this with 20000 fields.
gt;
gt; Obviously having the data in the correct format to begin with is helpful
gt; but sometimes you have to work with what you get. A workaround is to
gt; simply
gt; copy the data - paste into a text file and then bring it back to a
gt; spreadsheet where the destination fields aren't set as text. I strongly
gt; believe that there should be some sort of quot;executequot; function after
gt; changing
gt; cell formats to actually initiate the change since it doesn't occur
gt; automatically.
gt;
gt; Sorry for writing so much. Thanks in advance to any advice.Thanks Ragdyer, that works. Much appreciated.

I hope you don't mind but I'm going to push my luck here. Say that I want
to take that data and run a macro placing that data into another system.
Now, as the cells are now, they appear as say 35.05 but in the macro will
transfer as the true value such as 35.0468 - currently, if I run into this
issue, I will just copy this data into a text file where the 35.05 will be
displayed and bring it back over to excel and I'm in good shape.

This would also propose a problem if I wanted to do a lookup for example
where I actually have the data 35.05. So I'll write a vertical lookup
formula to search for my imported data against my data table and while they
look the same they won't produce a hit.

Thanks agian.quot;Ragdyerquot; wrote:

gt; Click in an unused *new* cell and format it to number with 2 decimals.
gt; Right click in this cell and choose quot;Copyquot;.
gt;
gt; Select your 2000 row column.
gt; Right click in the selection and choose quot;Paste Specialquot;.
gt; Click on quot;Addquot;, then lt;OKgt;, then lt;Escgt;.
gt;
gt; You now have *all* your text numbers as real numbers.
gt;
gt;
gt; --
gt; HTH,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt;
gt; quot;sjrkuquot; gt; wrote in message
gt; ...
gt; gt; I've been using excel for quite sometime and there has been one thing that
gt; gt; seriously irks me. Say, for example, I have a field (or for my purpose I
gt; gt; have a column of 20000 fields) and the data as it is now is in text
gt; gt; format.
gt; gt; A field for example is quot;29.665quot; (w/o quotes of course). If I choose to
gt; gt; copy
gt; gt; this field into a new field it will bring it over as text or whatever
gt; gt; format
gt; gt; the cell is originally will transfer over. This is fine and
gt; gt; understandable,
gt; gt; however, if I change the field format to numeric with 2 decimals for
gt; gt; instance, the field is still in its original format unless I double click
gt; gt; on
gt; gt; it, or click and hit F2 etc. So imagine doing this with 20000 fields.
gt; gt;
gt; gt; Obviously having the data in the correct format to begin with is helpful
gt; gt; but sometimes you have to work with what you get. A workaround is to
gt; gt; simply
gt; gt; copy the data - paste into a text file and then bring it back to a
gt; gt; spreadsheet where the destination fields aren't set as text. I strongly
gt; gt; believe that there should be some sort of quot;executequot; function after
gt; gt; changing
gt; gt; cell formats to actually initiate the change since it doesn't occur
gt; gt; automatically.
gt; gt;
gt; gt; Sorry for writing so much. Thanks in advance to any advice.
gt;
gt;

You could Round() *all* your values, both in the imported column and your
datalist ... couldn't you?

If that 2000 row column that you just converted is A1:A2000,
Enter this in B1:

=ROUND(A1,2)

Now, select B1 and *double* click on the quot;fill handlequot; (small black square
in lower right corner of selection),
which will automatically copy the formula in B1 down Column B, as far as
there is data in Column A.

Now, eliminate the formulas in Column B, leaving *only* the data behind.

While Column B is *still* selected from the copying, right click in the
selection and choose quot;Copyquot;,
Right click again and choose quot;Paste Specialquot;,
Click on quot;Valuesquot;, then lt;OKgt;, then lt;Escgt;.

You now have your data as true 2 decimal values!

You can do the same thing to your datalist,
*OR*
If the numbers in your datalist are the results of calculations, you could
include the Round() function into those formulas, so that the results would
also be *true*, 2 decimal numbers.

Something like this:
=ROUND(quot;your formulaquot;,2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
quot;sjrkuquot; gt; wrote in message
...
gt; Thanks Ragdyer, that works. Much appreciated.
gt;
gt; I hope you don't mind but I'm going to push my luck here. Say that I want
gt; to take that data and run a macro placing that data into another system.
gt; Now, as the cells are now, they appear as say 35.05 but in the macro will
gt; transfer as the true value such as 35.0468 - currently, if I run into this
gt; issue, I will just copy this data into a text file where the 35.05 will be
gt; displayed and bring it back over to excel and I'm in good shape.
gt;
gt; This would also propose a problem if I wanted to do a lookup for example
gt; where I actually have the data 35.05. So I'll write a vertical lookup
gt; formula to search for my imported data against my data table and while
they
gt; look the same they won't produce a hit.
gt;
gt; Thanks agian.
gt;
gt;
gt; quot;Ragdyerquot; wrote:
gt;
gt; gt; Click in an unused *new* cell and format it to number with 2 decimals.
gt; gt; Right click in this cell and choose quot;Copyquot;.
gt; gt;
gt; gt; Select your 2000 row column.
gt; gt; Right click in the selection and choose quot;Paste Specialquot;.
gt; gt; Click on quot;Addquot;, then lt;OKgt;, then lt;Escgt;.
gt; gt;
gt; gt; You now have *all* your text numbers as real numbers.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH,
gt; gt;
gt; gt; RD
gt; gt;
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
!
gt;
gt; --------------------------------------------------------------------------
-
gt; gt;
gt; gt; quot;sjrkuquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I've been using excel for quite sometime and there has been one thing
that
gt; gt; gt; seriously irks me. Say, for example, I have a field (or for my
purpose I
gt; gt; gt; have a column of 20000 fields) and the data as it is now is in text
gt; gt; gt; format.
gt; gt; gt; A field for example is quot;29.665quot; (w/o quotes of course). If I choose
to
gt; gt; gt; copy
gt; gt; gt; this field into a new field it will bring it over as text or whatever
gt; gt; gt; format
gt; gt; gt; the cell is originally will transfer over. This is fine and
gt; gt; gt; understandable,
gt; gt; gt; however, if I change the field format to numeric with 2 decimals for
gt; gt; gt; instance, the field is still in its original format unless I double
click
gt; gt; gt; on
gt; gt; gt; it, or click and hit F2 etc. So imagine doing this with 20000
fields.
gt; gt; gt;
gt; gt; gt; Obviously having the data in the correct format to begin with is
helpful
gt; gt; gt; but sometimes you have to work with what you get. A workaround is to
gt; gt; gt; simply
gt; gt; gt; copy the data - paste into a text file and then bring it back to a
gt; gt; gt; spreadsheet where the destination fields aren't set as text. I
strongly
gt; gt; gt; believe that there should be some sort of quot;executequot; function after
gt; gt; gt; changing
gt; gt; gt; cell formats to actually initiate the change since it doesn't occur
gt; gt; gt; automatically.
gt; gt; gt;
gt; gt; gt; Sorry for writing so much. Thanks in advance to any advice.
gt; gt;
gt; gt;