delete characters from string

[wizard0]

Dear all,

For example I have a string like:

C:/users/library/music/mp3/this-song.mp3

In my sheet I create a hyperlink with that path but I also need to create a hyperlink to the folder of the file.

I tried this:

=LEFT(C:/users/library/music/mp3/this-song.mp3,FIND(C:/users/library/music/mp3/this-song.mp3,"/")

This code gives me C: because the find function finds the first / How can I change the code so that it will keep searching the string for / and take all the chars left of the last one and the last / included.

I do something wrong, please help.

Kind regards,

Wizard0

[Paul_Hossler]

InStrRev() is your friend

Option Explicit
Sub Demo()
    Dim i As Long
    Dim s As String, s1 As String
    
    s = "C:/users/library/music/mp3/this-song.mp3"
    
    i = InStrRev(s, "/")
    
    s1 = Left(s, i - 1)
    MsgBox s1

End Sub

[snb]

Use 'basename' of the scripting library:

see:

http://www.snb-vba.eu/VBA_Bestanden.html#L_2


of

=LEFT(C:/users/library/music/mp3/this-song.mp3,FIND(C:/users/library/music/mp3/this-song.mp3,".")-1)

[mancubus]

if you need a worksheet formula rether than vba:

http://www.mrexcel.com/forum/excel-q...v-excel-2.html

posts 12 and 13.

[SamT]

This thread demonstrates what makes VBA Express the best integrated Microsoft Office site in the web.