Pull file names from list of URLs

[Kenichi]

Hello,

Say I have a list of image urls such as:

http://images.site.com/dd/ds/38232-01.jpg
http://images.site.com/adg/FD02_01.gif
http://images.site.com/cc/we/234294-DS.png
http://images.site.com/qwe/129382.jpg

How can I pull the data after the LAST / so that it leaves only the file name:

38232-01.jpg
FD02_01.gif
234294-DS.png
129382.jpg

Thanks in advance for your help!

 

[hiker95]

Kenichi,

Excel Workbook
	A	B
1	http://images.site.com/dd/ds/38232-01.jpg	38232-01.jpg
2	http://images.site.com/adg/FD02_01.gif	FD02_01.gif
3	http://images.site.com/cc/we/234294-DS.png	234294-DS.png
4	http://images.site.com/qwe/129382.jpg	129382.jpg
5
Sheet1

The formula in cell B1 copied down:
=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),100))

 

[hiker95]

Kenichi,

If you are looking for a macro solution:


Sampe raw data before the macro:

Excel Workbook
	A
1	http://images.site.com/dd/ds/38232-01.jpg
2	http://images.site.com/adg/FD02_01.gif
3	http://images.site.com/cc/we/234294-DS.png
4	http://images.site.com/qwe/129382.jpg
5
Sheet1

After the macro:

Excel Workbook
	A
1	38232-01.jpg
2	FD02_01.gif
3	234294-DS.png
4	129382.jpg
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub PullFileName()
' hiker95, 05/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=553493
Dim F As Variant, a As Long
Application.ScreenUpdating = False
F = Range("A1", Range("A" & Rows.Count).End(xlUp))
For a = LBound(F) To UBound(F)
  F(a, 1) = Trim(Right(Application.Substitute(F(a, 1), "/", Application.Rept(" ", 100)), 100))
Next a
Columns(1).ClearContents
Range("A1").Resize(UBound(F)).Value = F
Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the PullFileName macro.

 

[Kenichi]

Thanks! That works perfectly.