Strange behaviour with replace function
 

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

Strange behaviour with replace function
 

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

Strange behaviour with replace function
 

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



wrote:

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

--

Dave Peterson

Strange behaviour with replace function
 

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

Strange behaviour with replace function
 

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