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.


"Ragdyer" wrote:

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

Select your 2000 row column.
Right click in the selection and choose "Paste Special".
Click on "Add", then <OK, then <Esc.

You now have *all* your text numbers as real numbers.


--
HTH,

RD

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

"sjrku" wrote in message
...
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 "29.665" (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 "execute" 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.