Excel automatically changes the formatting of the cell to "Time"

S

Sam

Hello,

I have a long column of numbers [dates in the YY:DD format]. I wanted to
replace
":61" to ":59". Even though the cells are initially formatted as Text, as
soon as I make the change, Excel changes the formatting to Time, and the cell
with the change now has text ":59:00" in it.

Is it possible to force Excel to keep the cells formatted as Text?

Thank you!
 
E

Earl Kiosterud

Sam,

If the cells are truly formatted as text (Format - Cells - Number - Text),
the formatting should never change, and you should always see exactly what
you've typed. Give us an example of a cell before you've made the change,
and tell us what Format - Cells - Number tab indicates.
 
D

Dave Peterson

When you do an Edit|Replace, excel will "help" you and change the value to time
and it helped changing the cell's format to custom, too ([h]:mm:ss). (Well, it
did for me in xl2003).

Manually typing the new entry didn't do it. Edit|Replace was too helpful.



Earl said:
Sam,

If the cells are truly formatted as text (Format - Cells - Number - Text),
the formatting should never change, and you should always see exactly what
you've typed. Give us an example of a cell before you've made the change,
and tell us what Format - Cells - Number tab indicates.
--
Earl Kiosterud
www.smokeylake

Sam said:
Hello,

I have a long column of numbers [dates in the YY:DD format]. I wanted to
replace
":61" to ":59". Even though the cells are initially formatted as Text, as
soon as I make the change, Excel changes the formatting to Time, and the
cell
with the change now has text ":59:00" in it.

Is it possible to force Excel to keep the cells formatted as Text?

Thank you!
Click to expand...
Click to expand...
 
D

Dave Peterson

I think you could use a column of helper cells:

=substitute(a1,":61",":59")
(and dragdown)

Then edit|copy
and edit|Paste special|Values over the original range
(then delete that column of helper cells)

Or you could select the range and then run a macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim fStr As String
Dim tStr As String

fStr = ":59"
tStr = ":61"

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells _
(xlCellTypeConstants, xlTextValues)).Cells
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select some cells with values!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
.Value = Application.Substitute(.Value, fStr, tStr)
End With
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Hello,

I have a long column of numbers [dates in the YY:DD format]. I wanted to
replace
":61" to ":59". Even though the cells are initially formatted as Text, as
soon as I make the change, Excel changes the formatting to Time, and the cell
with the change now has text ":59:00" in it.

Is it possible to force Excel to keep the cells formatted as Text?

Thank you!
Click to expand...
 
Top