Sorting XML Sitemap URLs by Folder Depth

This can be handy if for example, you only want a list of products and they reside on folders that are 3 “/” deep into your URLs/Domain

For example:

• Myshop.com/categorypages/subcategorypage/productpage/

I only want the URLs that reside at the third level – i.e. /productpage/

1. Go to your XML sitemap – usually at Myshop.com/sitemap.xml
   
2. Right click and “save as” – save on your computer
   
3. Open Excel
   
4. Go to the Developer Tab (you might need to add this as it’s not there by default)
   
5. Click “Import”
   
6. Browse to find your sitemap.xml and import it into Excel
   
7. This usually pulls all your URLs into column 1 and other info like priority into separate columns
   
8. Delete all the columns except the first one with your URLs in it
   
9. Remove the https:// from the URLs with “find and replace” – On “Home” tab under “Find & Select” on the right
   
10. In cell B2 add the function:

=LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))

11. Drag the formula down the rest of column B

12. You can now order column B by the number of “/” found in each URL

If different categories have different folder structures then you can conditionally format and use different colours for different categories and then do a multiple criteria sort – by colour, then folder depth (column B)

You can download an example spreadsheet with the formula in here