Using Replace to remove part of a string
 

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.

Using Replace to remove part of a string
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

Using Replace to remove part of a string
 

From the Recorder:

Cells.Replace What:=";Color=12342", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
--
Gary''s Student - gsnu2007k


" wrote:

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.

Using Replace to remove part of a string
 

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


" wrote:

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.

Using Replace to remove part of a string
 

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


" wrote:

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.

Using Replace to remove part of a string
 

Thanks rick I don't know how that crept in

Mike

"Rick Rothstein (MVP - VB)" wrote:

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


" wrote:

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.