I have a column of numbers that are formatted as text. I can change the
format to number but the value does not change. What is the shortcut for
rentering the value that is currently in the cell?

Hi,
insert a column and enter the formula

=trim(A1)
copy it down, then copy and paste as values , then format as number

trim will eliminate any blank space

"Bzltyr" wrote:

I have a column of numbers that are formatted as text. I can change the
format to number but the value does not change. What is the shortcut for
rentering the value that is currently in the cell?

One way is to copy a blank cell. Select the column of numbers and right
clickPasteSpecialAdd..OK.

Another if error checking is enabled ;;select the column. Within the
selection the active cell should contain a number formatted as text. If you
notice a light yellow colored box appearing to the left side (with a green
triangle on the left top corner of the cell)..Click on the yellow box
(exclamation mark) and select convert to number.

If this post helps click Yes
---------------
Jacob Skaria


"Bzltyr" wrote:

I have a column of numbers that are formatted as text. I can change the
format to number but the value does not change. What is the shortcut for
rentering the value that is currently in the cell?

Thanks for the advice. There is a shortcut that all one has to do is
highlight the cell and hit the shortcut key and the same value that is in the
cell will just be renetered and will change from text to number.

Quite an onerous method if you have 1234000 cells<g

Go with the paste specialadd


Gord Dibben MS Excel MVP


On Thu, 1 Oct 2009 10:44:23 -0700, Bzltyr
wrote:

Thanks for the advice. There is a shortcut that all one has to do is
highlight the cell and hit the shortcut key and the same value that is in the
cell will just be renetered and will change from text to number.

You might try copying an empty cell, then selecting your column of "numbers"
and using Edit/ Paste Special/ Add
An alternative approach is to try Data/ Text to Columns
You might find that your cells contain spaces, or non-breaking spaces or
other non-numeric characters, so you might try a SUBSTITUTE or REPLACE
operation.
--
David Biddulph

"Bzltyr" wrote in message
...
I have a column of numbers that are formatted as text. I can change the
format to number but the value does not change. What is the shortcut for
rentering the value that is currently in the cell?

I am having the most difficult time. I have time values in the format
hh:mm:ss:sss, where the last three values are milliseconds. The problem is
that they are seperated from the seconds by a colon, which leaves me unable
to use them to calculate the differences between two of these times.
I need to find a way to either calcultate the time difference between the
two, or a way to get rid of the last kolon and millisecond values. Can
anyone help?
--
Regards,
Joseph


"Bzltyr" wrote:

I have a column of numbers that are formatted as text. I can change the
format to number but the value does not change. What is the shortcut for
rentering the value that is currently in the cell?

You could use a helper column of cells that changes the text values to real time
values. The formula would be something like:

=--SUBSTITUTE(A1,":",".",3)

The -- converts text to a real number (like multiplying by -1 twice).

The 3 indicates that you want the 3rd colon changed to a comma.

Then format the cell(s) with a custom format of: hh:mm:ss.000

You could actually embed this formula into any existing formula:

=a1-b1
would become:
=SUBSTITUTE(A1,":",".",3) - SUBSTITUTE(b1,":",".",3)
Format the cell with this formula nicely: hh:mm:ss.000
The subtraction will force excel to treat each as a number. The -- stuff won't
be necessary.

But I'd fix the data once so that I wouldn't have to worry about fixing all the
formulas (and new formulas).



Joseph Ryan wrote:

I am having the most difficult time. I have time values in the format
hh:mm:ss:sss, where the last three values are milliseconds. The problem is
that they are seperated from the seconds by a colon, which leaves me unable
to use them to calculate the differences between two of these times.
I need to find a way to either calcultate the time difference between the
two, or a way to get rid of the last kolon and millisecond values. Can
anyone help?
--
Regards,
Joseph

"Bzltyr" wrote:

I have a column of numbers that are formatted as text. I can change the
format to number but the value does not change. What is the shortcut for
rentering the value that is currently in the cell?

--

Dave Peterson

=--(LEFT(A1,LEN(A1)-4)&"."&RIGHT(A1,3)) and format the result as
hh:mm:ss.000
--
David Biddulph

"Joseph Ryan" wrote in message
...
I am having the most difficult time. I have time values in the format
hh:mm:ss:sss, where the last three values are milliseconds. The problem
is
that they are seperated from the seconds by a colon, which leaves me
unable
to use them to calculate the differences between two of these times.
I need to find a way to either calcultate the time difference between the
two, or a way to get rid of the last kolon and millisecond values. Can
anyone help?
--
Regards,
Joseph


"Bzltyr" wrote:

I have a column of numbers that are formatted as text. I can change the
format to number but the value does not change. What is the shortcut for
rentering the value that is currently in the cell?