How to Extract a Parent Project Folder Name from a SharePoint Excel Path
Question details
The user wants to automatically extract a parent project folder name (located two levels above the file) from a SharePoint document path using an Excel formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user has an Excel file saved in a multi-level SharePoint directory and needs a cell formula to dynamically display the name of the project folder.
- Observed behavior
- Standard Excel formulas like CELL("filename") return the server URL instead of a parsable local path, making it unreliable to extract specific parent folder names directly.
Check whether your SharePoint document library is synced to your local device, as formula-based path extraction behaves very differently between local drives and cloud-based URLs.
Use SharePoint Metadata Instead of Excel Formulas
Since Excel formulas cannot reliably parse SharePoint server paths, utilizing SharePoint's native metadata and exposing it to Excel is the most robust solution.
When an Excel file is stored in SharePoint, cloud URLs replace standard file paths. Attempting to parse these URLs with text-extraction formulas often leads to broken references when accessed by different users.
A better structural approach is to assign the project name as a metadata property directly within the SharePoint library.
Navigate to your SharePoint Document Library, click 'Add column', choose 'Text', and name it 'Project Name'.
Go to 'Library settings' > 'Column default value settings', select the project folder, and set the default value for the 'Project Name' column so any file placed inside inherits the name automatically.
Use the 'Export to Excel' button in the SharePoint ribbon to generate a query table that cleanly lists all files alongside their respective Project Name metadata.

Parse the Path Using Formulas (Local Sync Only)
If you ensure the SharePoint folder is mapped or synced locally using OneDrive, you can use a complex formula combination to extract the folder name.
Manage Spreadsheets Without Cloud Path Complications Using WPS Office
Struggling with complex SharePoint paths and unpredictable cloud formulas? WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office. It excels at local file management, ensuring your standard directory formulas work flawlessly without server-side URL interruptions.
- 1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files directly from your local drive.
- 3. Apply Standard Formulas: Use your standard text and lookup formulas confidently, knowing local paths will remain intact and parsable.

Frequently Asked Questions
Why does the CELL("filename") function return a URL instead of a local path?
When a file is stored in SharePoint or OneDrive and AutoSave is enabled, Microsoft Excel prioritizes the cloud location, returning an HTTPS URL. To obtain a local C: drive path, the file must be opened from a locally synced folder with AutoSave turned off.
Can I use Power Query to retrieve the SharePoint folder name?
Yes. By navigating to Data > Get Data > From File > From SharePoint Folder, you can use Power Query to connect to the directory. This imports file metadata, including accurate folder paths and parent folder names, directly into your spreadsheet without relying on volatile cell formulas.
Is there a specific text formula to extract a string between two slashes?
Yes, you can substitute the slashes with a large number of spaces using the SUBSTITUTE function, and then use the MID function to extract the desired chunk of text. However, this requires the folder structure and depth to remain perfectly consistent across all users.




