Cell autoformats when ":" is entered

[Exemplaris]

I have a Excel cell that has "1-1" in it. If I do a Find and Replace to change the "-" to ":" so I have "1:1" the formatting in the cell changes and will not display "1:1". I have tried a bunch of stuff but I'm not getting it. Is there somewhere that I can change this behavior?

 

[shg]

Works fine for me. What does it display for you?

 

[mikerickson]

What is in the cell?
When I enter 1-1 in a cell, Excel reads that as a date and enters the serial date for Jan 1.
Does your cell originally contain an excel serial date, formatted d-m or is there a preceding ' that forces 1-1 to be a string?

You mention that it won't display "1:1", what does it display?

 

[Exemplaris]

Well it depends on the formatting, usually it changes it to "1:01". I can get it to display "1:1" if i set the cell format to "text" and then enter "1:1".

So I select all of the used cells in the sheet set the format to "text" then try to do the "Replace" function and the cell format changes to "General" and the cell displays "0.04306" although the formula bar displays "0.0430555555555556".

I'm wondering how to stop it from changing the formatting on the cell when I try to do the "Replace" operation.

 

[mikerickson]

Try find "1-1" replace "'1:1", note the apostrophe.

It sounds like your current cells are formatted as Date.

 

[Exemplaris]

Yes that is what the formatting keeps getting changed to, or to General or Custom. How do I stop it from automatically changing?
Your suggestion for finding "1-1" and replacing with "'1:1" works but only if "1-1" is all I'm looking for. I have "1-2", "1-3" etc. to change also. Also if the cell has "Jumped 1-11" it'll find it it but changes it to "Jumped '1:11", note the apostrophe. If I try to find "-" and replace with ":" it changes "1-1" to "1':1" again the apostrophe.

There must be some setting that tells it to automatically change the formatting when the ":" is entered but I can't find it.

 

[shg]

Format the cells as Text beforehand.

 

[Rick Rothstein]

Yes that is what the formatting keeps getting changed to, or to General or Custom. How do I stop it from automatically changing?

If you enter your original data with a leading apostrophe (that is, '2-4 instead of 2-4) then replacing the dash with a colon will not change the data. If your data already exists because it is copied in rather than manually entered, then you can run this macro to do the replacements (Column A assumed to hold your data)...

Sub ReplaceDashWithColon()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("""'""&SUBSTITUTE(" & .Address & ",""-"","":"")")
  End With
End Sub