Function Problems

How to Extract a Parent Project Folder Name from a SharePoint Excel Path

Elise WilliamsElise Williams Oct 23, 2026 869 views

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.

How to Extract a Parent Project Folder Name from a SharePoint Excel Path
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a Custom Metadata Column

Navigate to your SharePoint Document Library, click 'Add column', choose 'Text', and name it 'Project Name'.

2
Assign Default Folder Values

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.

3
Export to Excel

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.

Use SharePoint Metadata Instead of Excel Formulas
Free Microsoft Office alternative

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. 1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite.
  2. 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files directly from your local drive.
  3. 3. Apply Standard Formulas: Use your standard text and lookup formulas confidently, knowing local paths will remain intact and parsable.
Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas.Reliable execution of file path formulas without forced cloud-URL conversions.Familiar user interface ensuring seamless migration with zero learning curve.Lightweight installation that opens large datasets significantly faster.
microsoft office alternative - wps office

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.