Data clean-up techniques in Excel: Extracting a filename from a URL

Sometimes you may have a list of URLs and you need to extract only the file name. In the following way, you can extract the file name from a URL. Assume cell A4 contains this URL: http://example.com/assets/images/horse.jpg

The following formula returns horse.jpg from the above URL:

=RIGHT(A4,LEN(A4)-FIND("*",SUBSTITUTE(A4,"/","*",LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))))

To understand this formula you have to know these four functions of Excel: RIGHT(), LEN(), FIND(), and SUBSTITUTE().

To make understand this process easier, we have broken the formula in different parts.

Download this excel file to see the breakdown of the formula

This formula returns actually all text that follows the last slash character. If cell A4 doesn’t contain a slash character, the formula will return an error.

To extract the URL without the file name, use this formula:
=LEFT(A4,FIND("*",SUBSTITUTE(A4,"/","*",LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))))

Extracting a file name from a URL

Extracting a file name from a URL.

Note: You can use Flash Fill technique to extract this type of data. Visit how to split text in Excel using Flash Fill

Happy Excelling 🙂

Read More…

Data clean-up techniques in Excel: Replacing or removing text in cells

Data clean-up techniques in Excel: Changing vertical data to horizontal data


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply