CODEWITHSUNDEEP
excelvba
Suresh Narasimman
Suresh Narasimman

Reputation: 11

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

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.

image shown here

Upvotes: 1

Views: 358

Answers (3)

Error 1004
Error 1004

Reputation: 8220

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

Upvotes: 1

user4039065
user4039065

Reputation:

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

Upvotes: 1

Irregular Expression
Irregular Expression

Reputation: 101

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.

Upvotes: 1

Related Questions