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 ?

 

[Peter_SSs]

Cheers. Thanks for the confirmation.

 

[AlexanderBB]

Peter, for a different purpose I've been trying to modify your RegExp to use a substring. But not succeeding (apart from looping with instr)
e.g. I send in the string and text to find and it returns any (vblf delimited) rows containing that text.
Is that possible ?
Thanks

 

[Peter_SSs]

From that description I am not entirely clear of exactly what your data is like originally nor exactly what it should be like at the end. Please refer to post #5.

 

[AlexanderBB]

Sorry, should have been more specific. I was hoping to create a standard routine
to extract a row from a string based on what I send in. This is an example - it's a vblf delimited string.

a) Ambrose & His Concert Orchestra v/Alan Kane (Decca F 8046)
b) Billy Cotton & His Band v/Alan Breeze (Rex 10085)
c) Carroll Gibbons & The Savoy Hotel Orpheans v/Leslie Douglas (Columbia FB 2748)
d) Flanagan & Allen (Decca F 8067)
e) Jack Daly (Regal Zonophone MR 3612)
f) Gay & Nevard (HMV BD 990)

And I could send in either
"d) " or "Flanagan & Allen" or "Decca F 8067"
to get the whole line "d) Flanagan & Allen (Decca F 8067)"

At the moment I'm splitting on vblf then using Filter. That many be good enough but I liked your
RegExp method very much and thought it more efficient.

 

[Peter_SSs]

I still cannot see exactly what is in what cells nor exactly where the vblf's are.
I also don't know what you mean by "I could send in"

Could you not show before/after with XL2BB?

 

[AlexanderBB]

It's a UDF in a module and the 'send in' is the argument passed to it.
I don't know what XL2BB is. The vblf are at the end of each row.

 

[Peter_SSs]

I don't know what XL2BB is

Have a look at the first bullet point in my signature block below.