VBA to remove characters from a comma separated list, where certain characters are found?

[RockandGrohl]

Hi all, need fast help on this as I'm under a timescale.

I have a list of ads due to be placed in a paper and under the "Pickups" for coach travel there is meant to be a list of coach-only pickups, but air and rail have slipped in.

As an example, please see the below:

[COLOR=#ff0000]Bury St Edmunds, Colchester, Ipswich, Flying from Norwich (NWI), Flying from Stansted (STN)[/COLOR]

So I need to remove everything after "Ipswich", including that last comma, I need to find the first instance of "Flying" and remove everything using that as a start point, to end up with the following:

[COLOR=#008000]Bury St Edmunds, Colchester, Ipswich[/COLOR]

This is more or less simple enough to do with FIND etc, but the problem is that sometimes the Airport or Rail pickups are buried in the middle, like this:

[COLOR=#ff0000]Birmingham, Flying from Birmingham (BHX), (RS) Birmingham New Street, Solihull, Sutton Coldfield[/COLOR]

Which would then need to be like this:

[COLOR=#008000]Birmingham, Solihull, Sutton Coldfield[/COLOR]

Can anyone help me urgently, I have to detect whether "Flying" or "(RS)" is within a set of commas (or at the end!) and then remove just those pickups including their respective commas to end up with just coach pickups (these don't have "(RS)" or "Flying from xxxx" in them)


Really appreciate it, thanks!

 

[RockandGrohl]

Re: [VBA] [Urgent] How to remove characters from a comma separated list, where certain characters are found?

That looks fine

Just tried the three variants on this string:

Glasgow, Flying from Glasgow (GLA), (RS) Glasgow Central, Flying from Edinburgh (EDI), (RS) Edinburgh

Every time, for Coach, Rail and Air I get the following result:

Glasgow

Seems odd, has been working perfectly up until this string!


EDIT: For some reason, the vbTextCompare = 1 had been reset to 0 on all counts.

I did not do this change and nobody else uses the sheets. Is this possible for the Function code to reset?

 

[Fluff]

Re: [VBA] [Urgent] How to remove characters from a comma separated list, where certain characters are found?

No, the code cannot change itself.