[VBA] Pulling data from middle of string

[RockandGrohl]

Hi guys,

Got this string:

\\chw-dc03\company\Sales\Faculty Documents\2019 Mailing Lists\64. LG XX Flying Scotsman Mailing list - 2019-06-12.csv

I need

64. LG XX Flying Scotsman

The string can also be like:

\\chw-dc03\company\Sales\Faculty Documents\2019 Mailing Lists\60. LG Vitalic mailing_list - 2019-06-01.csv
\\chw-dc03\company\Sales\Faculty Documents\2019 Mailing Lists\7. SV29 Mailing List - 2019-02-04.csv
\\chw-dc03\company\Sales\Faculty Documents\2019 Mailing Lists\38. LG Digitalism Mailing List - 2019-04-08.csv

Or many different variations. The common theme is that every string will have a final backslash and then either "Mailing" or "mailing"

I need what is in between, either "60. LG Vitalic" or "83. LG Digitalism" etc.

This is my code:

Range("B60").Value = facml

Str = Right(facml, Len(facml) - InStrRev(facml, "\", , 1))


Range("D4").Value = Str


spos = InStr(1, facml, "ists\") + 5
epos = InStr(spos, facml, "ailing_") - 2
camnam = Mid(facml, spos, epos - spos)




Range("B61") = camnam

On some it works ok, like "60. LG Vitalic", but on others it gives an error on the "camnam =" line (Invalid Procedure Call or Argument)

If anyone knows a better way to get what's between the final "" and the "ailing " I would really appreciate it

Thanks!

 

[Fluff]

How about

Sub RockandGrohl()
   Dim Cl As Range
   Dim Sp As Variant
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Sp = Split(Cl.Value, "\")
      Cl.Offset(, 1).Value = Trim(Left(Sp(UBound(Sp)), InStr(1, Sp(UBound(Sp)), "mailing", vbTextCompare) - 1))
   Next Cl
End Sub

 

[RockandGrohl]

How about

Sub RockandGrohl()
   Dim Cl As Range
   Dim Sp As Variant
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Sp = Split(Cl.Value, "\")
      Cl.Offset(, 1).Value = Trim(Left(Sp(UBound(Sp)), InStr(1, Sp(UBound(Sp)), "mailing", vbTextCompare) - 1))
   Next Cl
End Sub

Thanks for this, think we had some crossed wires, I don't have a list of strings, I just provided examples of strings I can receive.

I'm only looking to convert one string at a time.

Also, the trim should be on "ailing" not "mailing" as it can be uppercase or lowercase

As a result I'd like to trim to start from the a of "ailing" and go back two spaces, so it trims off the space and the "M" or "m"

So "s\2019 Mailing Lists\8. LG Kia mailing" becomes "8. LG Kia" (notice the lack of space after the "Kia"

And when it splits, can this then be defined as "camnam"?

 

[Fluff]

Just change it to

Sub RockandGrohl()
   Dim Facml As String, Camnam As String
   Dim Sp As Variant
      
   Sp = Split(Facml, "\")
   Camnam = Trim(Left(Sp(UBound(Sp)), InStr(1, Sp(UBound(Sp)), "mailing", vbTextCompare) - 1))
End Sub

where Facml is the starting string

 

[RockandGrohl]

Just change it to

Sub RockandGrohl()
   Dim Facml As String, Camnam As String
   Dim Sp As Variant
      
   Sp = Split(Facml, "\")
   Camnam = Trim(Left(Sp(UBound(Sp)), InStr(1, Sp(UBound(Sp)), "mailing", vbTextCompare) - 1))
End Sub

where Facml is the starting string

Works perfectly, you are a gent and a scholar.

 

[Fluff]

You're welcome & thanks for the feedback