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!

 

[Fluff]

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

How about

Function Rock(cl As Range) As String
   Dim sp As Variant
   Dim i As Long
   sp = Split(cl, ", ")
   For i = 0 To UBound(sp)
      If InStr(1, sp(i), "Flying", vbTextCompare) = 0 And InStr(1, sp(i), "(RS)", vbTextCompare) = 0 Then
         Rock = Rock & sp(i) & ", "
      End If
   Next i
   Rock = Left(Rock, Len(Rock) - 2)
End Function

used like
=Rock(A2)

 

[RockandGrohl]

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

Thanks Fluff,

I'm not sure I understand, Rock?

Or =Rock(A2)?

 

[Fluff]

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

You put that in the worksheet like a formula

Book1
	A	B
2	Bury St Edmunds, Colchester, Ipswich, Flying from Norwich (NWI), Flying from Stansted (STN)	Bury St Edmunds, Colchester, Ipswich
3	Birmingham, Flying from Birmingham (BHX), (RS) Birmingham New Street, Solihull, Sutton Coldfield	Birmingham, Solihull, Sutton Coldfield
Roster

Cell Formulas
Range	Formula
B2	=rock(A2)

 

[RockandGrohl]

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

You put that in the worksheet like a formula

	A	B
Bury St Edmunds, Colchester, Ipswich, Flying from Norwich (NWI), Flying from Stansted (STN)	Bury St Edmunds, Colchester, Ipswich
Birmingham, Flying from Birmingham (BHX), (RS) Birmingham New Street, Solihull, Sutton Coldfield	Birmingham, Solihull, Sutton Coldfield

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Roster​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=rock(A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This has literally just blown my entire mind to ****, I had no idea you could do this.

INCREDIBLE.

Thank you!

 

[Rick Rothstein]

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

I'm not sure I understand, Rock?

Fluff needed to give his function a name, so he used the "Rock" from your forum name "RockandGrohl"

Or =Rock(A2)?

What Fluff posted is called a UDF... user defined function. Here is something that may help you use it...

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Rock just like it was a built-in Excel function. For example,

=Rock(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Fluff]

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

You're welcome & thanks for the feedback.

 

[RockandGrohl]

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

Fluff needed to give his function a name, so he used the "Rock" from your forum name "RockandGrohl"




What Fluff posted is called a UDF... user defined function. Here is something that may help you use it...

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Rock just like it was a built-in Excel function. For example,

=Rock(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Haha, yeah I got the first part, I just had 0 knowledge of UDF's... As soon as Fluff posted the example it clicked.

As a bonus I demonstrated it to some colleagues and their jaws dropped - was completely out of our collective conscious that that could be done! Hats off to Fluff!

 

[RockandGrohl]

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

You're welcome & thanks for the feedback.

Hi Fluff,

Just to confirm I'm not doing anything catastrophically wrong..

String:

Lichfield, Flying from East Midlands (EMA), Flying from Birmingham (BHX), (RS) Birmingham New Street

This contains 1 Coach, 2 Air and 1 Rail Pickup

Function Coach(cl As Range) As String   Dim sp As Variant
   Dim i As Long
   sp = Split(cl, ", ")
   For i = 0 To UBound(sp)
      If InStr(1, sp(i), "Flying", vbTextCompare) = 0 And InStr(1, sp(i), "(RS)", vbTextCompare) = 0 Then
         Coach = Coach & sp(i) & ", "
      End If
   Next i
   Coach = Left(Coach, Len(Coach) - 2)
End Function


Function Rail(cl As Range) As String
   Dim sp As Variant
   Dim i As Long
   sp = Split(cl, ", ")
   For i = 0 To UBound(sp)
      If InStr(1, sp(i), "Flying", vbTextCompare) = 0 And InStr(1, sp(i), "(RS)", vbTextCompare) = 1 Then
         Rail = Rail & sp(i) & ", "
      End If
   Next i
   Rail = Left(Rail, Len(Rail) - 2)
End Function


Function Air(cl As Range) As String
   Dim sp As Variant
   Dim i As Long
   sp = Split(cl, ", ")
   For i = 0 To UBound(sp)
      If InStr(1, sp(i), "Flying", vbTextCompare) = 1 And InStr(1, sp(i), "(RS)", vbTextCompare) = 0 Then
         Air = Air & sp(i) & ", "
      End If
   Next i
   Air = Left(Air, Len(Air) - 2)
End Function

So now when I enter =Coach(A1), =Air(A1) or =Rail(A1), it successfully amends the string to only include one of the three types of pickups.

Just to confirm, this is correct? I've done a couple tests and it works, but wanted to make sure I'm not glossing over something important.

 

[Fluff]

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

That looks fine