Path returned for current file but one folder up

Coll4208

New Member
Joined
Oct 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I need to return a text string path for the current file, but one folder up.

I have found: =LEFT(CELL("filename",P1),FIND("[",CELL("filename",P1))-1)

and even =HYPERLINK("..","Open One Folder Up")

The first one will return the Path in text for the current file folder, and the second one is a hyper link. How can I get the Path for the Hyperlink to show?

Thank you!!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Your formula
Excel Formula:
=LEFT(CELL("filename",P1),FIND("[",CELL("filename",P1))-1)
will return the folder that the workbook file is in.

If you want to return the folder above it, you will need something like this.
Excel Formula:
=LEFT(CELL("filename"),FIND("|",SUBSTITUTE(CELL("filename"),"\","|",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))-1)))
 
Upvote 0
Thank you, that works, but I have NO idea why or what it is saying. LOL! Thank you
Your formula
Excel Formula:
=LEFT(CELL("filename",P1),FIND("[",CELL("filename",P1))-1)
will return the folder that the workbook file is in.

If you want to return the folder above it, you will need something like this.
Excel Formula:
=LEFT(CELL("filename"),FIND("|",SUBSTITUTE(CELL("filename"),"\","|",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))-1)))
The above Formula works in my table, but for some reason it isn't working in Power Query. For example:

In the imported table, Folder above is C:\Users\Owner\Budget 2023\CAMWorkingFIles\Budget2023\
But in Power Query it is: C:\Users\Owner\Budget 2023\CAMW

Can you tell me why the rest is missing? I am *thinking* it is because I don't completely understand the formula you wrote for me. Thank you!!
 
Upvote 0
Another way to write the formula, if you are using O365, is with the 'Let' function

Excel Formula:
=LET(FilePath,CELL("filename"),LEFT(FilePath,FIND("|",SUBSTITUTE(FilePath,"\","|",LEN(FilePath)-LEN(SUBSTITUTE(FilePath,"\",""))-1))))

No reason it should be different in PowerQuery unless the filename changes. But I am no PQ expert. I'd check to see whether the Cell function returns a different value under PQ

Excel Formula:
=CELL("filename")
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top