How to extract the filename of a full path in Excel worksheets

Question

I need to extract the filenames of column B for example extract pm5a1tktzlrzzgno2r5l.png from v1448360146/pm5a1tktzlrzzgno2r5l.png and compare that result to the value in column A.

Answer 1

Split off the filename and extension then find a match in column A.

sub huh()

    dim m as variant, str as string, i as long

    with worksheets("sheet1")
        for i = 1 to .cells(.rows.count, "B").ens(xlup).row
            str = split(.cells(i, "B").value2, chr(47))(ubound(split(.cells(i, "B").value2, chr(47))))
            m = application.match(str, .range("A:A"), 0)
            if not iserror(m) then
                'm is the row number in column A of the matching filename
                debug.print .cells(m, "A").value
                'do something with it
            end if
        next i
    end with

end sub

Answer 2

Change Sheet Name, Set the right range and try:

Sub Test()

Dim Lr As Long
Dim Position As Long
Dim str As String
Dim i As Long

With Sheets("Sheet1") '<= Change SheetName if needed
    Lr = .Cells(.Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lr
        Position = InStrRev(.Cells(i, "B").Value, "/") + 1
        str = Mid(.Cells(i, "B").Value, Position, ((Len(.Cells(i, "B").Value)) - (Position - 1)))
        If str = .Cells(i, "A").Value Then
            MsgBox "Same!"
        End If
    Next i
End With

End Sub

Answer 3

Open your Excel file, press Alt+F10 (or start your VBEditor from panel), and there create a new module (Insert > new Module). Into a new module put this code:

Sub extractFileNames()
Dim rng As Range

'Set your own input range with A and B columns and worksheet name here.
Set rng = ThisWorkbook.Worksheets("Your_worksheet_name").Range("A1:B1000")

Dim arr As Variant
arr = rng.Value2  

For i = 1 To UBound(arr)
    Dim tmp() As String
    tmp = Split(arr(i, 2), "/")
    arr(i, 1) = tmp(UBound(tmp))      
Next i
rng.Value2 = arr 
End Sub

You can run this macro with any supported way you want (see the link, if you don't know how).

Also, if you don't want to use macros in your file, you may try to use functions like =RIGHT($B2,LEN($B2) - FIND("/", $B2)) , but that won't be helpful if you have too many paths or they have different number of delimeters.