Using Replace to remove part of a string

[Magnus.Moraberg]

Hi,

I have a number of cells that end with ;Color=12342. For example -

Alan;Color=12342
Jane;Color=12342
Clare;Color=12342
Ian;Color=12342

I want to replace ";Color=12342" with "". So that the 4 cells contain
-

Alan
Jane
Clare
Ian

The following code doesn't work (replaces nothing) -

range.Replace(";Color=12342", "", Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, false, false, false, true);

And this line of code inserts a space at the end of each cell, which I
don't want -

range.Replace(";Color=12342", " ", Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, false, false, false, true);

Why does the first line of code above not work?

Thanks,

Barry.

 

[Don Guillett]

I just recorded this and it worked just fine.

Sub Macro10()
'
' Macro10 Macro
' Macro recorded 8/18/2008 by Donald B. Guillett
'

'
Range("A2:A6").Select
Selection.Replace What:=";color=12342", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
cleaned up

Range("A2:A6").Replace ";color=12342", "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

 

[Gary''s Student]

From the Recorder:

Cells.Replace What:=";Color=12342", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Mike H]

Hi,

You don't need code you can use

=LEFT(A1,FIND(";",A1)-1)

But if you want code try

Sub sonic()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
On Error Resume Next
For Each c In myrange
c.Value = Left(c.Value, InStr(4, c.Value, ";", 1) - 1)
Next
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

There is need to specify a starting position of 4 in your Instr function
call in this line of code...

c.Value = Left(c.Value, InStr(4, c.Value, ";", 1) - 1)

....the default start position of 1 would work fine. I would use this
instead...

c.Value = Left(c.Value, InStr(c.Value, ";") - 1)

As a matter of fact, if the person's name was short, like Jo, then your
original code would not remove the desired part.

Rick

 

[Mike H]

Thanks rick I don't know how that crept in

Mike

There is need to specify a starting position of 4 in your Instr function
call in this line of code...


....the default start position of 1 would work fine. I would use this
instead...

c.Value = Left(c.Value, InStr(c.Value, ";") - 1)

As a matter of fact, if the person's name was short, like Jo, then your
original code would not remove the desired part.

Rick