How to Extract Text After the Last Slash in an Excel URL
Question details
The user needs to extract the specific text, such as an ID or file name, located after the final forward slash in a list of URLs within an Excel spreadsheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Parsing a column of URLs to isolate the final segment into a separate column, even when the URLs contain varying numbers of forward slashes.
- Observed behavior
- The text appearing after the last '/' in a URL string is isolated and returned as a clean text string in the target cell.
Ensure your URLs are uniformly formatted as text strings. Check which version of Excel you are using, as the newest functions like TEXTAFTER are only available in recent versions.
Extract Using TRIM, RIGHT, and SUBSTITUTE
This classic formula combination works perfectly in all versions of Excel to isolate the text following the last slash.
This method replaces all forward slashes with a large number of spaces, extracts the rightmost portion of the new string, and then removes the extra spaces to leave only the desired text.
Click on the cell where you want the extracted text to appear (for example, cell B1).
Type the formula =TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",300)),300)) into the formula bar, assuming your URL is located in cell A1.
Press Enter to see the extracted text. Click the small square at the bottom-right corner of the cell and drag it down to apply the formula to all other URLs in your list.
Extract Using the TEXTAFTER Function
If you are using a newer version of Excel or WPS Office, the TEXTAFTER function provides a much simpler and cleaner formula.
Extract Text from URLs Faster in WPS Spreadsheet
WPS Spreadsheet fully supports advanced text functions, including TRIM, SUBSTITUTE, and TEXTAFTER, allowing you to manipulate strings and URLs effortlessly. Enjoy a highly compatible alternative for your data processing tasks.
- 1. Open your Spreadsheet: Launch WPS Office and open the document containing your list of URLs.
- 2. Enter the Formula: Select an empty cell next to the first URL and type =TEXTAFTER(A1,"/",-1).
- 3. Get the Result: Press Enter to instantly retrieve the final part of the URL.
- 4. Fill the Column: Double-click the fill handle in the corner of the cell to process the entire column automatically.
Frequently Asked Questions
Can I extract text between two specific slashes instead of the last one?
Yes. You can use a combination of MID and FIND, or the newer TEXTBEFORE and TEXTAFTER functions in combination, to isolate text between any two specific delimiters in a URL string.
Why is the TRIM and SUBSTITUTE formula returning an error or empty result?
This usually happens if the cell reference is incorrect, or if the target cell is actually blank or does not contain any forward slashes. Ensure the cell contains the correct URL string and that there are no hidden formatting issues.
Does this formula method work for extracting file names from local computer paths with backslashes?
Yes, you can use the exact same formulas for local file paths. Just replace the forward slash ("/") with a backslash ("\") in the SUBSTITUTE or TEXTAFTER formula.
