Extracting part of a string

[AlexanderBB]

Can't quite decide on a best method to achieve this.
I have a string delimited by vblf characters
One of the rows may start with "All" then " * by *"
If true, I want to get everything after "by " to end of that row (it will never be the last row in the string).
Not sure if RegEx or Filter will do it, or just split on vblf and check each row. Or something else?

Any suggestions ?

 

[Phuoc]

Like this?

=REPLACE(A1,1,SEARCH("by",A1,SEARCH("All",A1)+3)+3,"")

 

[AlexanderBB]

Looks good and concise but will that work in VBA or just as a worksheet function?
This is in a module and the string is not in a sheet,

 

[Phuoc]

One way:

Public Function ExtractingPart(s As String) As String
Dim v As Variant
For Each v In Split(s, vbLf)
If v Like "All*by*" Then
    ExtractingPart = VBA.Mid(v, InStr(1, v, "by") + 3)
    Exit For
End If
Next
End Function

 

[Peter_SSs]

If the above does not resolve your question, please give us a smallish but representative set of sample data AND the expected results with XL2BB so that we can be sure of your data structure and expected results and can test with that sample data.

 

[AlexanderBB]

Hey, I like it! Much better than I had... For each is brilliant as there is a varying number of rows
Many thanks.

 

[Peter_SSs]

Perhaps your data could never have anything like this but note the incorrect result from that previous UDF in C1 below.
It could be corrected as follows (& used in column D below).

Public Function ExtractPart(s As String) As String
Dim v As Variant
For Each v In Split(s, vbLf)
If v Like "All* by *" Then
    ExtractPart = VBA.Mid(v, InStr(1, v, " by ") + 4)
    Exit For
End If
Next
End Function

But just as an option, and since you mentioned RegEx, here is a non-looping alternative function as used in column B below.
BTW, does "All" at the beginning on the line need to have a space after it? You didn't indicate that here but I'm wondering ..

One of the rows may start with "All" then " * by *"

Function GetPart(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = Replace("(^|#)(All.* by )(.+?)(?=#)", "#", vbLf)
    If .Test(s) Then GetPart = .Execute(s)(0).SubMatches(2)
  End With
End Function

AlexanderBB.xlsm
	A	B	C	D
1	One or two words Allow that abysses are dangerous All other by this time are gone	this time	ses are dangerous	this time
2	one or two words Any other by this time are gone
3	All other by this time are gone	this time	this time	this time
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=GetPart(A1)
C1:C3	C1	=ExtractingPart(A1)
D1:D3	D1	=ExtractPart(A1)

 

[AlexanderBB]

That's even better solution Peter, as no looping or split needed (and I do need to test 2 strings)
Amazing what RegEx or do. And that anyone understands it all ! Yes there'll always be a space after the "All".
Thanks very much

 

[Peter_SSs]

Yes there'll always be a space after the "All".

Then try this version

Function GetPart(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = Replace("(^|#)(All .* by  )(.+?)(?=#)", "#", vbLf)
    If .Test(Replace(s, " ", "  ")) Then GetPart = Mid(.Execute(Replace(s, " ", "  "))(0).SubMatches(2), 2)
  End With
End Function

Note that there are two space characters where highlighted

AlexanderBB.xlsm
	A	B
1	One or two words Allow that and by the way All by yourself Go	yourself
2	One or two words All other by this time Allow that and by the way Go	this time
3	All by yourself go by train	yourself
4	All other by this time are gone	this time
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=GetPart(A1)

 

[AlexanderBB]

Thank you for extra info. All working nicely