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

 

[Dave Peterson]

I use xl2003 and you can toggle a Lotus 123 transition setting to avoid this:

Tools|Options|Transition tab
Check transition formula entry

Do the edit|replace

And change the setting back to off.

In code:

ActiveSheet.TransitionFormEntry = True
Range("a:A").Replace What:=";Color=12345", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ActiveSheet.TransitionFormEntry = False

 

[Barb Reinhardt]

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\)]

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

Rick