PDA

View Full Version : delete characters from string



wizard0
10-07-2015, 12:32 PM
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
10-07-2015, 02:06 PM
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
10-07-2015, 02:07 PM
Use 'basename' of the scripting library:

see:

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

(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)
(http://www.snb-vba.eu/VBA_Bestanden.html#L_2)

mancubus
10-07-2015, 02:31 PM
if you need a worksheet formula rether than vba:

http://www.mrexcel.com/forum/excel-questions/76765-equivalent-instr-instrrev-excel-2.html

posts 12 and 13.

SamT
10-07-2015, 03:39 PM
This thread demonstrates what makes VBA Express the best integrated Microsoft Office site in the web.