Bulk Remove the Root of a URL in seconds

I used to spend ages manually cleaning up long lists of URLs in my spreadsheets. You know the drill – every link starting with the same https://www.example.com/ prefix, making it impossible to quickly see the actual unique pages! It was such a headache, especially when I needed to audit a website or plan content, and accidentally adding an e2%80%8b character or other junk only made it worse. I even looked for a dedicated 'url removal tool' but found that simple solutions were often the best. Then, I discovered this incredible Google Sheets trick using the built-in 'Find and Replace' feature, and it completely changed my workflow. It's not just about removing invalid URLs; it's about making your valid URLs manageable and clear! It's so efficient, you’ll wonder how you ever managed without it. Here’s how you can do it too, in just a few simple steps: Open Your Google Sheet: First, make sure your URLs are in a column in your Google Sheet. Select Your Data: Highlight the column or specific range of cells containing the URLs you want to clean up. This is important to ensure you're only modifying the intended links. Access 'Find and Replace': Go to the 'Edit' menu at the top of your Google Sheet. From the dropdown, select 'Find and replace'. A small pop-up window will appear. Define What to 'Find': In the 'Find' field, enter the common root of the URL you want to remove. For example, if all your links start with https://sheetsbyolan.myshopify.com/products/, you would type that exact string into the 'Find' box. Make sure to include any slashes or other characters that are part of the common root. Define What to 'Replace With': This is the magic step! Leave the 'Replace with' field *empty*. This tells Google Sheets to delete whatever it finds in the 'Find' field. Set Your Options: 'Search' dropdown: Ensure it's set to 'Specific range' if you selected cells, or 'All sheets'/'This sheet' if you want to apply it broadly. I usually stick to 'Specific range' for safety. 'Match case': If the root URL might appear with different capitalization (unlikely for URLs, but good to know), you can check this. For standard URLs, it’s usually not necessary. 'Also search within links': This is CRUCIAL! Make sure this box is checked. This ensures that the 'Find and Replace' operation actually works on the underlying hyperlink, not just the displayed text. Execute the Replacement: Click the 'Replace all' button. Google Sheets will then show you a confirmation message, something like "Replaced X instances of [your root URL]". And just like that, your root URLs are gone, leaving behind only the unique paths – and yes, they remain clickable links! This method is perfect for extracting parts of a string and making your data much more readable and actionable. No more manually deleting https://sheetsbyolan.myshopify.com/products/content-planner-1 to get just content-planner-1. It helps you easily identify unique product pages, blog posts, or categories. While it doesn't directly act as an 'invalid url removed' tool, by stripping away the noise, any truly broken or malformed URLs will stand out much more clearly. It’s a simple, fast, and incredibly effective way to streamline your URL management in Google Sheets!