Find second word "by" then delete to comma

[Steved]

Hello from Steved
Need to please develop the below to get a result as show'n in the 2 examples
below

Sub Secondby()
ActiveDocument.Range.Find.Execute _
FindText:="(\(by*) by *\)", _
ReplaceWith:="\1)", _
MatchWildcards:=True, _
Replace:=wdReplaceAll

End Sub



From this
Beau Son= by Balmerino by Silver Marie, $, L 1:0:0, R 0:0:0
Royal Awahuri= by Kingdom Bay by Princess Zam, $, L 1:0:0, R 0:0:0

To this
Beau Son= by Balmerino, $, L 1:0:0, R 0:0:0
Royal Awahuri= by Kingdom Bay, $, L 1:0:0, R 0:0:0

Thankyou.

 

[Pecoflyer]

Don't know about VBA but this formula does the trick

Code
-------------------
=LEFT(A3;FIND("@";SUBSTITUTE(A3;"by";"@";2))-2)&RIGHT(A3;LEN(A3)-FIND("$";A3)+3
-------------------

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answer

 

[Steved]

Hello Pecoflyer from Steved

This is great for a spreadsheet but I have it in word.

I thankyou for giving me an ulternitave

 

[Pecoflyer]

Sorry, didn't realize emb

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answer

 

[Tony Jollans]

I think this will do it for you:

Sub Secondby()
ActiveDocument.Range.Find.Execute _
FindText:="(* by *) by *,", _
ReplaceWith:="\1,", _
MatchWildcards:=True, _
Replace:=wdReplaceAll

End Sub

 

[H. Druss]

Hello from Steved
Need to please develop the below to get a result as show'n in the 2
examples
below

Sub Secondby()
ActiveDocument.Range.Find.Execute _
FindText:="(\(by*) by *\)", _
ReplaceWith:="\1)", _
MatchWildcards:=True, _
Replace:=wdReplaceAll

End Sub



From this
Beau Son= by Balmerino by Silver Marie, $, L 1:0:0, R 0:0:0
Royal Awahuri= by Kingdom Bay by Princess Zam, $, L 1:0:0, R 0:0:0

To this
Beau Son= by Balmerino, $, L 1:0:0, R 0:0:0
Royal Awahuri= by Kingdom Bay, $, L 1:0:0, R 0:0:0

Thankyou.

This works in VB. Does VBA have instring reverse?
*******************************************************************
Private Sub Command1_Click()
Dim s As String, s1 As String
Dim iBy As Long, iComma As Long

s = "Beau Son = by Balmerino by Silver Marie, $, L 1:0:0, R 0:0:0"

s1 = "Royal Awahuri= by Kingdom Bay by Princess Zam, $, L 1:0:0, R 0:0:0"

iBy = InStrRev(s, "by")
iComma = InStr(s, ",")

MsgBox Left$(s, iBy - 2) & Mid$(s, iComma)

'To this
'Beau Son= by Balmerino, $, L 1:0:0, R 0:0:0
'Royal Awahuri= by Kingdom Bay, $, L 1:0:0, R 0:0:0

End Sub
********************************************************************
Good luck
Harold

 

[Steved]

Hello Tony from Steved

I Thankyou

All the best for 2009

 

[Steved]

Hello H. Druss from Steved

I Thankyou

All the Best for 2009