Extract string after last / charachter in string

[yomero]

I have been not coding for a while and I can't find the answer to this one.

I have a list of paths and files in an excel sheet, which is a dump from a server I don't have access to.

I need to separate the name of the file from the path, as you can see the file is always after the "/" charachter. I can not use text to columns, because some cells have different lengths and different number of "/" in the string.

Oh yes, it would be better to do it with VBA, but if you have a formula that will work...however it is like 50,000 rows and formulas bloat the file size unnecesaryly. Thanks

Samples of the strings:
ep/ics/dls/en/2.eps
ep/ics/dls/asdasdasd.eps
ssss/dvxcv/dvsfdvsxv/sadavf.eps

 

[njimack]

Here's a formula approach.

Excel Workbook
	A	B	C	D
1	ep/ics/dls/en/2.eps			2.eps
2	ep/ics/dls/asdasdasd.eps			asdasdasd.eps
3	ssss/dvxcv/dvsfdvsxv/sadavf.eps			sadavf.eps
Sheet1

 

[mikerickson]

If you prefer VBA

Dim wholeString as String, lastSection as String
Dim Sections as Variant

Sections = Split(wholeString,"/")
lastSection = Sections(UBound(Sections))

Note: in general, it is better to use native Excel function than it is to use VBA.

 

[T. Valko]

I have been not coding for a while and I can't find the answer to this one.

I have a list of paths and files in an excel sheet, which is a dump from a server I don't have access to.

I need to separate the name of the file from the path, as you can see the file is always after the "/" charachter. I can not use text to columns, because some cells have different lengths and different number of "/" in the string.

Oh yes, it would be better to do it with VBA, but if you have a formula that will work...however it is like 50,000 rows and formulas bloat the file size unnecesaryly. Thanks

Samples of the strings:
ep/ics/dls/en/2.eps
ep/ics/dls/asdasdasd.eps
ssss/dvxcv/dvsfdvsxv/sadavf.eps

Here's a formula method.

Book1
	A	B
2	ep/ics/dls/en/2.eps	2.eps
3	ep/ics/dls/asdasdasd.eps	asdasdasd.eps
4	ssss/dvxcv/dvsfdvsxv/sadavf.eps	sadavf.eps
Sheet1

Formula entered in B2 and copied down:

=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100))

 

[yomero]

Thank you everyone for your swift responses!

ym

 

[Peter_SSs]

For a vba solution, just building on Mike's idea, I think you would find this pretty fast even over 50,000 rows. It assumes that you just want the filename extracted and that the column immediately to the right of your data is available to house the results.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ExtractName()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <br>    <SPAN style="color:#00007F">Const</SPAN> Col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C" <SPAN style="color:#007F00">'<-Orig data column</SPAN><br>    <br>    lr = Range(Col & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range(Col & 1).Resize(lr)<br>        a = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lr<br>            <SPAN style="color:#00007F">If</SPAN> Len(a(i, 1)) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                b = Split(a(i, 1), "/")<br>                a(i, 1) = b(UBound(b))<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Offset(, 1).Value = a<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Apart from vba or formula, you could do this very quickly manually as follows:

1. Copy the column of data (not even needed if you don't want to retain the original full path and name)

2. Select thye column by clicking its heading label.

3. Find/Replace with
Find what: */
Replace with: leave blank
Check in Options that 'Match entire cell contents' is NOT checked
Replace All


Just in case you wanted the path and file name in the next 2 columns, you could try this vba variation.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ExtractPathAndName()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <br>    <SPAN style="color:#00007F">Const</SPAN> Col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C" <SPAN style="color:#007F00">'<-Orig data column</SPAN><br>    <br>    lr = Range(Col & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range(Col & 1).Resize(lr, 2)<br>        a = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lr<br>            b = Split(a(i, 1), "/")<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(b) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                a(i, 2) = b(UBound(b))<br>                b(UBound(b)) = vbNullString<br>                a(i, 1) = Join(b, "/")<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Offset(, 1).Value = a<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>