No, I am pretty sure not (even if you remove the two 'C' references that you
accidentally left inside the Left function call).

Rick


"Barb Reinhardt" wrote in message
...
Rick,

Couldn't you also do it this way?

With Worksheets("Sheet4").Range("A1:A10")
.NumberFormat = "@"
.Value = Left(C.Value, InStr(C.Value, ";") - 1)
End with

Barb Reinhardt



"Rick Rothstein (MVP - VB)" wrote:

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


wrote in message
...
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