Using Replace to remove part of a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Magnus.Moraberg@gmail.com - 18 Aug 2008 15:34 GMT

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.

Reply to this Message

Don Guillett - 18 Aug 2008 15:45 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 29 lines]
>
> Barry.

Reply to this Message

Gary''s Student - 18 Aug 2008 15:51 GMT

From the Recorder:

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

Signature

Gary''s Student - gsnu2007k

> Hi,
>
[quoted text clipped - 29 lines]
>
> Barry.

Reply to this Message

Mike H - 18 Aug 2008 15:55 GMT

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

> Hi,
>
[quoted text clipped - 29 lines]
>
> Barry.

Reply to this Message

Rick Rothstein (MVP - VB) - 18 Aug 2008 16:17 GMT

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

> Hi,
>
[quoted text clipped - 48 lines]
>>
>> Barry.

Reply to this Message

Mike H - 18 Aug 2008 17:06 GMT

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...
[quoted text clipped - 63 lines]
> >>
> >> Barry.

Reply to this Message