How to Remove SharePoint Subfolder URLs in Excel When Parent Exists
Question details
The user needs to clean a list of SharePoint folder paths in Excel by retaining only the highest-level parent folder URLs and removing any associated subfolder URLs.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Deduplicating and organizing a list of SharePoint folder paths to keep only top-level parent directories.
- Observed behavior
- The Excel column contains a mix of parent and subfolder URLs, requiring a programmatic way to filter out child paths without corrupting legitimate folder names.
Before removing any URLs, ensure you have backed up your original dataset. Standardize your list by trimming all trailing slashes from the paths so that parent and subfolder strings can be accurately compared.
Use Power Query to Filter by Prefix and Length
Power Query provides the most reliable way to sort URLs and filter out child paths using text prefix logic without breaking legitimate folder names.
Power Query can dynamically analyze each URL against previously loaded URLs. By sorting paths from shortest to longest, parent folders are processed first, allowing you to discard any subsequent URL that starts with a parent's exact string plus a forward slash.
Select your column of SharePoint URLs, navigate to the Data tab, and click 'From Table/Range' to open the Power Query Editor.
Go to Add Column > Custom Column. Use the formula Text.Length([URL]) to calculate the character count of each path.
Click the drop-down on your new Length column and select 'Sort Ascending'. This guarantees parent folders appear before their subfolders.
Add a step to check if the current row's URL starts with any previously retained URL using the Text.StartsWith function. Filter out the rows where this evaluates to true.
Remove the helper Length column, click 'Close & Load' from the Home tab, and place your deduplicated parent-only paths back into the worksheet.

Use a VBA Macro to Delete Subfolder Rows
VBA automates the logical comparison of URLs, programmatically deleting rows that contain subfolders of a preceding parent path.
Use Dynamic Arrays (With Caution)
For newer versions of Excel, dynamic array formulas like UNIQUE and LET can assist in basic cleanups, though they are less robust than Power Query.
Manage Complex Spreadsheet Data Smoothly with WPS Office
If handling advanced data cleaning and SharePoint integrations in Excel feels clunky or expensive, WPS Office provides a lightweight, highly compatible, and free alternative. Experience seamless spreadsheet management with full support for advanced formulas and macros.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer.
- 2. Open Your Data File: Launch WPS Spreadsheets and open your .xlsx file containing the SharePoint URLs.
- 3. Apply Formulas: Use built-in text filtering functions or advanced array formulas directly within the familiar spreadsheet interface.

Frequently Asked Questions
Why shouldn't I use formulas that remove numbers to clean folder paths?
Removing digits from folder paths (for instance, using regular expressions to strip out numbers) can corrupt valid SharePoint folder names that legitimately include numbers (e.g., '2023_Budget' becomes '_Budget'). This leads to broken links and lost data.
Can I just use Excel's Remove Duplicates feature?
No. Excel's 'Remove Duplicates' feature only deletes rows that are exact, identical matches. It will not recognize that a subfolder path (like /sites/Marketing/Budget) is logically a child or duplicate of a parent folder path (like /sites/Marketing).
How does standardizing trailing slashes help in filtering URLs?
Standardizing paths by ensuring they either all have or all lack a trailing slash (e.g., making sure '/sites/Marketing/' and '/sites/Marketing' format identically) prevents the system from failing to match parent directories accurately during text comparisons.
What is the best way to sort the URL list before processing?
You should sort the URLs by their text length, from shortest to longest. This guarantees that parent folders (which are shorter) appear before their respective subfolders, making it logically easier to identify and remove the child paths in sequence.




