Preventing auto date formatting

  • Thread starter Thread starter Frank Marousek
  • Start date Start date
F

Frank Marousek

Excel keeps on insisting on formatting my data as date without asking!

This is happening in two specific cases:

1) (which I have asked about here previously) When I paste data from the web
into Excel that contains data such as 1-3, Excel insists on re-formatting it
as 3-Jan. It doesn't matter if I preformat the cells to Text-- the
reformatting still occurs.

2) When I have existing data such as 3/4 V, and I use the Replace command to
strip the V, Excel reformats the remainder as 4-Mar. Again, the cells in
question are formatted as Text before using the Replace command.

Does anyone know of any way to keep Excel from doing this?

Thanks.
 
Sorry but I don't have a way to make Excel not date format. For the second
problem I can only suggest a macro. This is something I just threw together
but maybe it'll be useful. It does require that the cells have the Text
format or else it behaves like Excel.

--
Jim Rech
Excel MVP

Sub BetterRemove()
Dim Cell As Range
Dim SrcRg As Range
Dim ReplaceText As String
ReplaceText = InputBox("Remove what text?")
If ReplaceText <> "" Then
If Selection.Cells.Count = 1 Then
Set SrcRg = Cells.SpecialCells(xlCellTypeConstants)
Else
Set SrcRg = Selection.SpecialCells(xlCellTypeConstants)
End If
For Each Cell In SrcRg
Cell.Value = Application.Substitute(Cell.Value, ReplaceText, "")
Next
End If
End Sub
 
Back
Top