Extract File Extensions from URLs
I have a list of image URLs and need to extract the file type (e.g., .jpg, .png) from the end of the string. This is crucial for categorizing assets, checking file formats, or preparing data for content management systems.
=RIGHT(A2, LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))+0) How it works: This formula works by cleverly identifying the position of the *last* dot in a URL. It first counts all dots in the URL. Then, it uses `SUBSTITUTE` to replace only the *last* dot with a unique placeholder character ('~'). By finding the position of this placeholder, we effectively pinpoint the start of the file extension. The `LEN` function then calculates the total length of the URL, and by subtracting the position of the placeholder, we determine exactly how many characters from the right end constitute the file extension (including the dot). The `RIGHT` function then extracts precisely that many characters, giving you the desired file type.
Data Setup
| URL |
|---|
| https://www.example.com/images/product-123.jpg |
| https://cdn.marketingassets.net/promo/banner_ad_v2.png?id=123 |
| https://storage.cloud.io/documents/report_q3_2023.pdf |
| https://marketing.site/videos/intro_clip.mp4 |
| https://www.anotherdomain.org/assets/logo.svg |
Step-by-Step Guide
**Step 1: Identify the Last Dot:** The core challenge is to find the *last* occurrence of a dot ('.') in the URL, as URLs can have multiple dots (e.g., 'example.com/image.jpg'). We achieve this by temporarily replacing the last dot with a unique character (like '~') using `SUBSTITUTE(A2,".","~",LEN(A2)-LEN(SUBSTITUTE(A2,".","""))`. The `LEN(A2)-LEN(SUBSTITUTE(A2,".","""))` part counts the total number of dots in the string, telling `SUBSTITUTE` which occurrence to replace.
**Step 2: Find the Position of the Last Dot:** Use the `FIND("~", ...)` function to locate the position of our unique character ('~'), which now marks the position of the original last dot.
**Step 3: Calculate the Length of the Extension:** Subtract the position of the last dot (found in Step 2) from the total length of the URL (`LEN(A2)`). This gives us the number of characters from the last dot to the end of the string. We add `0` to this result to include the dot itself in the extracted string.
**Step 4: Extract the Extension:** Finally, use the `RIGHT(A2, calculated_length)` function to extract the specified number of characters from the right end of the URL in cell A2. Enter this formula into cell B2 and drag down to apply to your entire list of URLs.
Explore More
Create Comma-Separated Keyword Tags
I have a column of individual keywords and need to combine them into a single cell separated by commas for meta tags.
Find Campaign ROI by ID (Return 0 if Missing)
I need to look up the ROI for a campaign ID, but if the ID isn't found, I want it to return '0' instead of an error.
Map Lead Source to CRM Code
I have a list of lead sources like 'Facebook' and need to find their corresponding numeric code from a reference table.
Count Leads from Specific Source
I need to count how many leads in my monthly report came specifically from 'Organic Search'.