Changing cell format - for example text to numeric

[sjrku]

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.

[Ragdyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[sjrku]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

[RagDyer]

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 "fill handle" (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 "Copy",
Right click again and choose "Paste Special",
Click on "Values", then <OK>, then <Esc>.

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("your formula",2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"sjrku" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >