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