CODEWITHSUNDEEP
excelvba
Abhijeet
Abhijeet

Reputation: 269

Not able to split for multiple special characters

I am trying to split cell value up to last "\" in string 'C:\Users\punateng\Desktop\Pending Spec Updates.xlsx' in cell A1 so that result in cell B1 should be 'C:\Users\punateng\Desktop\'

I have written below code:

Sub mu()
Cells(1, 2).Value = Split(Cells(1, 1).Value, "\")
End Sub

But i am getting result as C: in cell B1.

Please help.

Upvotes: 1

Views: 282

Answers (2)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

This is a case where a function is better than a sub.

Excel has Find and Search functions but doesn't have a FindLast function.

In a User Defined Function (UDF) you can use some functions that aren't available in the Application.WorksheetFunction collection. One of them is InstrRev which finds the position of the first instance of a string like "\" reading backwards from the end of the string. Using This little gem of knowledge and text editing functions you can build this UDF:

Function FileNameFromPath(Path1 As String)
    'Test if you have been parsed an network file path
    If InStr(1, Path1, "\") > 0 Then
        'Get whatever is after the last "\"
        FileNameFromPath = Right(Path1, Len(Path1) - InStrRev(Path1, "\"))
    Else
        'Could be a SharePoint file with http// address
        If InStr(1, Path1, "\") > 0 Then
            'Get whatever is after the last "/"
            FileNameFromPath = Right(Path1, Len(Path1) - InStrRev(Path1, "/"))
        Else
            'There isn't a path separator there
            FileNameFromPath = "That ain't a path"
        End If
    End If
End Function

So you can call this UDF in any cell in your workbook by typing "=fi", hitting Tab to paste it into your cell, then selecting the cell you want to test and enter a end bracket ")".

Upvotes: 2

asongtoruin
asongtoruin

Reputation: 10359

Split returns a string array, which in this case will consist of each part of the input text that was previously separated by a \ - so, you are actually returning the array {"C:";"Users";"punateng";"Desktop";"Pending Spec Updates.xlsx"}. When you try to paste this into Cells(1,2), VBA just interprets this as being the first element of the string array.

Instead, you might want to try

Cells(1,2).Value=Left(Cells(1,1).Value,InstrRev(Cells(1,1).Value,"\")-1)

which should find the last instance of \ and return the text before it.

Upvotes: 2

Related Questions