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.