Manipulating Text Strings; InStr InStrRev Mid Right Left Len!! (1 Viewer)

[Oreynolds]

I am going mad here trying to work out the InStr, InStrRev, Len, Mid, Right, Left VBA functions!!! Can you help end my struggles please……!?

I have a foldername = "c:\TEST\tempfoldera\" (example)

And using one/mix of the above functions I want to retrieve everything AFTER the 2nd “\” from the RIGHT HAND end of the string, so:

NewString = “Tempfoldera\”

I need to use the \ as obviously the foldernames could be longer and shorter so the 2nd \ from the right is in my case always a fixed reference point.

Fingers crossed!

 

[Gasman]

Use Split() and take the 3rd entry which will number 2 as it starts at 0.?
However what happens if you have more than that number of folders?

Also this would work?

tt= "c:\TEST\tempfoldera\" 
tt1= Mid(tt,instr(4,tt,"\")+1)
? tt1
tempfoldera\

HTH

 

[Oreynolds]

Use Split() and take the 3rd entry which will number 2 as it starts at 0.?
However what happens if you have more than that number of folders?

Also this would work?

tt= "c:\TEST\tempfoldera\"
tt1= Mid(tt,instr(4,tt,"\")+1)
? tt1
tempfoldera\

HTH

Thankyou. I will sleep easy after all! Appreciated

 

[Oreynolds]

Use Split() and take the 3rd entry which will number 2 as it starts at 0.?
However what happens if you have more than that number of folders?

Also this would work?

tt= "c:\TEST\tempfoldera\"
tt1= Mid(tt,instr(4,tt,"\")+1)
? tt1
tempfoldera\

HTH

Emmm, sorry still struggling with this as follows:

foldername = "F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCC)\"
foldernamenew = "F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCCXX)\"

The above are my two folder paths. I need two functions:

1) To take from foldername up to the second \ from the RIGHT, so; J32944 Centenary House Crawley (WSCC)\
2) To take from foldernamenew everything from the second \ from the RIGHT back to the beginning of string, so; F:\SFA 2021\Running projects\SSE Contracting Ltd

Any ideas? Sorry - its late and brains gone to sleep!!

 

[linxpatrick]

Incomplete code

Dim lngPosition as Long
Dim strPath as String

strPath = "C:\TEST\tempfoldera\"

lngPosition = InStr(1, strFolder, "\") 'This returns the position of the first backslash.
lngPosition = Instr(lngPosition + 1, strFolder, "\") 'The position for the second back-slash
strPath = Mid(strPath, lngPosition + 1) 'strPath could has been defined as a parameter to the function or as I have defined it here.

This could be expanded to where the backslash to test for is included as a numeric function parameter. Testing and setting lngPosition could then be put into a loop that exits once the nth backshash is found.

 

[MajP]

I made this a little involved to handle potential problems
I find the second part first and then eliminate to find you first part.

Handles
Null
F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCC)\
F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCC)
F:\SFA 2021\
F:\SFA 2021
F:SFA 2021

Public Function GetSecondPart(FolderName As Variant) As String
  Dim arrParts() As String
  Dim count As Integer

  If Not IsNull(FolderName) Then
    arrParts = Split(FolderName, "\")
    count = UBound(arrParts)
    If Right(FolderName, 1) = "\" Then
     If count > 0 Then GetSecondPart = arrParts(count - 1)
    Else
      If count > 0 Then GetSecondPart = arrParts(count)
    End If
  End If
End Function
Public Function GetFirstPart(FolderName As Variant) As String
  Dim arrParts() As String
  Dim count As Integer
  Dim SecondPart As String
  If Not IsNull(FolderName) Then
    SecondPart = GetSecondPart(FolderName)
    arrParts = Split(FolderName, "\")
    count = UBound(arrParts)
    If count = 1 Or (count = 2 And Right(FolderName, 1) = "\") Then
      GetFirstPart = FolderName
    Else
      GetFirstPart = Replace(FolderName, SecondPart, "")
    End If
    'Handle an exception where no \ at end
    If Right(GetFirstPart, 2) = "\\" Then GetFirstPart = Left(GetFirstPart, Len(GetFirstPart) - 1)
  End If
End Function

Public Sub TestPart()
  Dim strFolderName As Variant
  strFolderName = "F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCC)\"
  Debug.Print GetSecondPart(strFolderName)
  Debug.Print GetFirstPart(strFolderName)
  Debug.Print
  'Test with no \ at end
  strFolderName = "F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCC)"
  Debug.Print GetSecondPart(strFolderName)
  Debug.Print GetFirstPart(strFolderName)
  Debug.Print

  'make sure handles null
  strFolderName = Null
   Debug.Print GetSecondPart(strFolderName)
  Debug.Print GetFirstPart(strFolderName)
  Debug.Print

  'Make sure handles One \
  strFolderName = "F:\SFA 2021"
  Debug.Print GetSecondPart(strFolderName)
  Debug.Print GetFirstPart(strFolderName)
  Debug.Print

  'Make sure handle no \
  strFolderName = "F:SFA 2021"
  Debug.Print GetSecondPart(strFolderName)
  Debug.Print GetFirstPart(strFolderName)
  Debug.Print
  'Test second part
End Sub

Here are my results of the test

J32944 Centenary House Crawley (WSCC)
F:\SFA 2021\Running projects\SSE Contracting Ltd\

J32944 Centenary House Crawley (WSCC)
F:\SFA 2021\Running projects\SSE Contracting Ltd\

'there was a space here for a null string

SFA 2021
F:\SFA 2021


F:SFA 2021

 

[moke123]

Sub Test()
    Dim strX As String
    'strX = "F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCC)\"
    strX = "F:\SFA 2021\Running projects\SSE Contracting Ltd\J32944 Centenary House Crawley (WSCCXX)\"

    Debug.Print SplitMyStringLSide(strX, 3)
    Debug.Print SplitMyStringRSide(strX, 4)

End Sub
Function SplitMyStringRSide(StrIN As String, Pos As Integer) As String
    Dim var As Variant
    Dim i As Integer
    Dim strOut As String

    If Right(StrIN, 1) = "\" Then   'strip off last \
        StrIN = Left(StrIN, Len(StrIN) - 1)
    End If

    var = Split(StrIN, "\")

    If Pos > UBound(var) Then MsgBox "Error": Exit Function

    For i = Pos To UBound(var)

        strOut = strOut & var(i) & "\"

    Next i

    SplitMyStringRSide = strOut

End Function

Function SplitMyStringLSide(StrIN As String, Pos As Integer) As String
    Dim var As Variant
    Dim i As Integer
    Dim strOut As String

    If Right(StrIN, 1) = "\" Then   'strip off last \
        StrIN = Left(StrIN, Len(StrIN) - 1)
    End If

    var = Split(StrIN, "\")

    If Pos > UBound(var) Then MsgBox "Error": Exit Function

    For i = 0 To Pos

        strOut = strOut & var(i) & "\"

    Next i

    SplitMyStringLSide = strOut

End Function

F:\SFA 2021\Running projects\SSE Contracting Ltd\
J32944 Centenary House Crawley (WSCCXX)\

 

[Mike Krailo]

I like the way you handled that Moke123. Can you tell me why you elected to use a variant instead of an array directly?

If you don't know the total number of string parts for that SplitMyStringRSide function, then you would need a CountStringParts function as well. Then you could just change -1 to -2, or -3 etc... to lop off string parts from the Right.

SplitMyStringRSide(strX, CountStringParts(strX, "\")-1)

Public Function CountStringParts(MyString As String, Delim As String) As Integer
   Dim S1 As String
   Dim StartNum As Integer
   Dim PartsArr() As String
 
   On Error GoTo ErrorHandler
 
   StartNum = Len(MyString)
 
   ' Does string end with delimiter?
   If Right(MyString, 1) = Delim Then
      ' There is a trailing delimeter, remove it
      S1 = Left(MyString, StartNum - 1)
      If Left(MyString, 1) = Delim Then
         ' There is a leading delimiter, remove it
         S1 = Right(S1, StartNum - 2)
      End If
     
      If Len(S1) < 1 Then
         CountStringParts = 0
      ElseIf Len(S1) < 2 Then
         CountStringParts = 1
      Else
         ' Find number of parts in the string
         PartsArr = Split(S1, Delim)
         CountStringParts = UBound(PartsArr) + 1
      End If

   Else
      If Left(MyString, 1) = Delim Then
         ' There is a leading delimiter, remove it
         S1 = Right(MyString, StartNum - 1)
      Else
         S1 = MyString
      End If
     
      If Len(S1) < 1 Then
         CountStringParts = 0
      ElseIf Len(S1) < 2 Then
         CountStringParts = 1
      Else
         ' Find number of parts in the string
         PartsArr = Split(S1, Delim)
         CountStringParts = UBound(PartsArr) + 1
      End If
     
   End If
   Exit Function

ErrorHandler:
   Select Case Err.Number
      Case 5 'invalid procedure call
         CountStringParts = 0
         Exit Function
      Case Else
         MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Oops, we found an ERROR"
         Resume Next
   End Select
End Function

 

[The_Doc_Man]

I'm confused about why folks took such a torturous path.
This next is AIR code and has no variable declarations.
If the string to be searched is stLongPath and you want the result in stPortion...

lngLPLen = Len( stLongPath )                            'find length of string
lngX = InStrRev( stLongPath, "\" )                     'find 1st (rightmost) \
lngY = InStrRev( stLongPath, "\", lngX )            'find 2nd (2nd rightmost) \
stPortion = Right( stLongPath, (lngLPLen - lngY) )    'extract right-hand-side

Then if you find that you are getting one more character than you want (i.e. the 2nd slash) you subtract 1 from (lngLPLen-lngY) before taking the Right function.

InStrRev function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Right function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

You can test for bad assumptions (like not having 2 slashes in the string) by examining the return values of lngX and lngY, so at most two more IF statements, one after each InStrRev call.

 

[Mike Krailo]

That's because, I forgot about InStrRev Doc. Good point.

 

[moke123]

I blame Gasman, He was the first one to mention Split()

 

[MajP]

I'm confused about why folks took such a torturous path

Normally on this forum I assume people are calling these functions from a query, and thus data is various and imperfect.
1. Must handle nulls and not bomb out
2. Must handle bad data such as no "\" at end
3. Must handle various amounts of "\" and not hard coded solutions with specified amounts

Every time I write the simple as asked solution the OP comes back, and "what about this case...". So I demoed the likely cases.

 

[The_Doc_Man]

True, it gets more complex if inside a query. You CAN nest them, except that then you have to repeat several of the InStrRev since you can't so easily trap a return value. Which is why the Access gods created UDFs.

The OP didn't mention a query environment in the first post and all of the replies seemed to be veering that way. If this IS a query environment, then of course UDFs would be better. If this is just a VBA sequence, what I showed (perhaps with a couple of error tests) might suffice.