get path for subfolder

[twistsd]

I have an excel file stored on OneDrive in a folder, which has a subfolder called FolderA which has subfolders called site1, site2 etc. I can get the file path for the main file to display in a cell and then concatenate and add the subfolders into another cell. The issue I have is sometimes my pc opens the file and the path is C:\\folder..... and other times its https://onderivefolder, the problem is the "/" & "\" in the concatenate and how to get the correct one depending upon which path type is displayed. Any help on how to best address this would be gratefully appreciated.
Thanks

 

[earthworm]

give both complete path here

 

[twistsd]

give both complete path here

The full paths are:
C:\Users\S\OneDrive - xxx.org.uk\Plan\Files\Circuit

https://s.sharepoint.com/personal/s_org/Documents/Plan/Circuit

 

[earthworm]

Explain what's happening . Is the file saved on your pc desktop and what error is appearing.

 

[twistsd]

Explain what's happening . Is the file saved on your pc desktop and what error is appearing.

The file is stored on OnDrive.

I have a cell with

=LEFT(@CELL("filename"),FIND("[",@CELL("filename"),1)-1)

which gives the following C:\Users\S\OneDrive - S.org.uk\Plan\ if I open the file from file explorer but if I open the file form within Excel I get https://s-my.sharepoint.com/personal/s_xx_org_uk/Documents/Circuit/ .

Within that folder I have a subfolder which then has 4 other folders for output from the Excel file to be stored. If I use

=CONCATENATE(B5,"Images")

for the subfolder that works it adds the subfolder name but how do I get it to add the correct path separator after the subfolder name?

Thanks

 

[earthworm]

still not clear . Please give one example with answer manually as to what patch you want to appear in excel.

 

[twistsd]

still not clear . Please give one example with answer manually as to what patch you want to appear in excel.

If I open the file from my laptop from file explorer the path is shown as C:\Users\S\OneDrive - S.org.uk\Plan\ if I open it from within Excel or on a mac the path is shown as https://s-my.sharepoint.com/personal/s_xx_org_uk/Documents/Circuit/ so trying to use the concatenate formula to add the two subfolders is proving difficult as it will only work for either the local path or OneDrive path.

If I add "SubFolder\Folder\" in the concatenate formula I get C:\Users\S\OneDrive - S.org.uk\Plan\SubFolder\Folder\ which is fine but with the OneDrive path it shows as https://s-my.sharepoint.com/personal/s_xx_org_uk/Documents/Circuit/SubFolder\Folder\ and if it try to add "SubFolder/Folder/" in the concatenate formula I get C:\Users\S\OneDrive - S.org.uk\Plan\SubFolder/Folder/ or https://s-my.sharepoint.com/personal/s_xx_org_uk/Documents/Circuit/SubFolder/Folder/ .

So what I want to be able to do is add subfolder & folder to the path in the correct format for both local & OneDrive.

Thanks

 

[earthworm]

I am not diving deep . just try substitute formula to substitute "/" with "\" and vice versa using if error formula as an advantage.

 

[twistsd]

I am not diving deep . just try substitute formula to substitute "/" with "\" and vice versa using if error formula as an advantage.

Thanks.

How would I generate that to run when the file opens?

 

[earthworm]

Thanks.

How would I generate that to run when the file opens?

If the link changes upon opening file i don't think you can do that because the link is appearing on another platform / browser.
Have you tried browser (From Web) within excel . I am just exploring . This is not a solution