Excel 2007 reformatting cells

B

Bzltyr

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?
 
E

Eduardo

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
 
J

Jacob Skaria

One way is to copy a blank cell. Select the column of numbers and right
click>PasteSpecial>Add..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
 
B

Bzltyr

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.
 
D

David Biddulph

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.
 
G

Gord Dibben

Quite an onerous method if you have 1234000 cells<g>

Go with the paste special>add


Gord Dibben MS Excel MVP
 
J

Joseph Ryan

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?
 
D

Dave Peterson

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).
 
D

David Biddulph

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