Excel 2007 : Parse string of text from one cell

**ssim** · 02-19-2009, 11:16 PM

I have the path for some of my data in column A and it ends with a back slash "\". I have been trying to find a way to start at the right of a cell and backtrack to the second occurrence of this and extract the text up to right until you hit the next/last occurrence of it. I have attached a sample spreadsheet with sample data and the desired result.

I have spent a few hours trying to nail this one down and searched all the help and forums that I can find.

Any assistance would be greatly appreciated.

**protonLeah** · 02-20-2009, 01:53 AM

Here's a small user function to search from the end of the string:
USAGE:
Given a path in cell B4, then in C4: "=GetFolder(B4)"

Option Explicit
Public Function GetFolder(ByVal CellAddr As Range) As String
    Dim StrLen      As Long, _
        kounter     As Long, _
        TestPath    As String, _
        Ptr         As Long
        TestPath = CellAddr.Value
        StrLen = Len(TestPath) - 1
        For kounter = StrLen To 1 Step -1
            If Mid(TestPath, kounter, 1) = "\" Then Exit For
            Ptr = Ptr + 1
        Next kounter
        GetFolder = Mid(TestPath, kounter + 1, Ptr)
End Function

**DonkeyOte** · 02-20-2009, 05:02 AM

An approach using native formulae:

C4:
=TRIM(MID(SUBSTITUTE(B4,"\",REPT(" ",100)),100*(LEN(B4)-LEN(SUBSTITUTE(B4,"\",""))-(RIGHT(B4,1)="\")),100))

An alternative UDF:

Function LastFolder(rngPath As Range)
LastFolder = Split(rngPath, "\")(Len(rngPath) - Len(Replace(rngPath, "\", "")) - Abs(Right(rngPath, 1) = "\"))
End Function

Called from cell