Strange behaviour with replace function

[Magnus.Moraberg]

Hi,

I'm trying to replace cells with the following format -

78/23/21;Color=12345

with this -

78/23/21

to do this I do -

Range("A1:A1").Select
Selection.Replace What:=";Color:12345", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

The problem is that-

28/05/14;Color=12345

formats to a date after the replace -

2028/05/14

If you set the replace function to have a replace format of text, the
replace no longer replaces the text and the following -

Range("A1:A1").Select
Selection.NumberFormat = "General"
Application.ReplaceFormat.NumberFormat = "@"
Selection.Replace What:=";Color:12345", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=True

gives you -

28/05/14;Color=12345

with the formatting of the cell having been changed to Text...

How might I replace -

28/05/14;Color=12345

with -

28/05/14

Thanks,

Barry

 

[Rick Rothstein \(MVP - VB\)]

I'm pretty sure this code does what your original code was attempting to do
(relying only on the presence of the semi-colon to work), just change the
sheet and cell reference to what your situation requires)...

Dim C As Range
......
......
For Each C In Worksheets("Sheet4").Range("A1:A10")
C.NumberFormat = "@"
C.Value = Left(C.Value, InStr(C.Value, ";") - 1)
Next

Rick