Returning Parts of Variable-Length File Paths

[btorrean]

Dear Forum Users,


I have a question about returning parts of a file path from a formula that references the path as it exists in another cell on a worksheet. I have tried using 'Find' and 'Search' within a formula to search for the slashes and return the item between the slashes. My problem is, though, that the portions of the file paths are variable in length. The parts of the file paths that I want returned are the same "level" of depth, however. So I always want to return the portion that is five levels deep. Like I said, I've tried using Find and Search, but when the parts of the path are variable and "deep" within the path, that's a ton of Finds and Searches. Does anyone have an idea of an easier way that I could do this?


Brian


Brian J. Torreano

 

[btorrean]

Dear Everybody,


I found a solution! I realized that in VBA, returning parts of a file path are simple if you use the Split() function. By creating a User-Defined Function (UDF) using Split(), I could use VBA to return the value of a portion of the file path passed to it via a formula. I can't believe this is so simple! Here's a screenshot of the data and the UDF in action. Also below is the code. I hope you find this helpful! I think this has broader applications than just returning parts of a file path. You could return any portion of a string that you want, as long as the string has a delimiter. If you do find this helpful, please let me know! Thanks!


<

>

Option Explicit
Option Compare Text


Public Function ReturnSplit(strInput As String, strDelim As String, intReturnPart As Integer) As String
' Purpose: User-Defined Function (UDF) to return a portion of a string that can be split by a delimiter.
'          This is particularly useful for returning parts of a file path.
' Inputs: strInput = The string that you want to return a part of.  In this case, a portion of the file path.
'         strDelim = The string delimiter that "splits" the portions of the input string.  In this case it 
'                    is a backslash "\".
'         intReturnPart = An integer representing the portion of the string you want returned.  As the code is,
'                         it is set up to return the first portion if the integer "1" is passed, or the third
'                         portion if the integer "3" is passed.  If you want to return items by the index number,
'                         eliminate the " - 1" portion from the line of code below.  The parentheses must remain
'                         around "intReturnPart" if you do that, however.
' Output: A portion of the delimited string.
' Created by: Brian J. Torreano (BT) 01/11/2017
' Last Update by: Brian J. Torreano (BT) 02/08/2017
On Error GoTo ReturnSplit_Error:


' Return the value we want.  If there is an error in any portion of this line of code, it goes to
'   the error-trapping portion of the code below and returns the error string.
ReturnSplit = Split(strInput, strDelim)(intReturnPart - 1)


ReturnSplit_Exit:


    ' Exit the function.
    Exit Function
    
ReturnSplit_Error:


    ' If we've gotten here, there was an error in the code.  Return the error string.
    ' NOTE: Do NOT replace the error string return here with a message box!  Doing so would cause a message box
    '       to pop up when you run the calling formula for EVERY instance of the forumula in the workbook!
    ReturnSplit = "#ERROR"
    
    '...and exit.
    GoTo ReturnSplit_Exit
    
End Function

Brian

Brian J. Torreano