Formula for filename doesn't give local path to file in recent Excel release

%3CLINGO-SUB%20id%3D%22lingo-sub-1384211%22%20slang%3D%22en-US%22%3EFormula%20for%20filename%20doesn't%20give%20local%20path%20to%20file%20in%20recent%20Excel%20release%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1384211%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eusing%20this%20to%20get%20path%20and%20filename%20(%3DLEFT(%40CELL(%22filename%22%2CC10)%2CFIND(%22%5B%22%2C%40CELL(%22filename%22%2CC10))-1))%20only%20returns%20a%20SharePoint%20web%20address%20when%20the%20file%20is%20opened%20locally%20on%20a%20synced%20SharePoint.%3C%2FP%3E%3CP%3EIt%20used%20to%20give%20me%20the%20local%20path%20which%20is%20needed%20in%20Power%20Query.%3C%2FP%3E%3CP%3EWhy%20isn't%20this%20working%20anymore%3F%20It%20usually%20does%2C%20although%20it's%20a%20bit%20random%20at%20times.%20Reopening%20the%20file%20usually%20works%20in%20those%20cases.%3C%2FP%3E%3CP%3EBut%20now%20it's%20always%20the%20SharePoint%20link.%3C%2FP%3E%3CP%3EHow%20can%20this%20be%20changed%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVersion%20is%202004%20(Build%2012730.20236)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EDaniel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1384211%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1385978%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20filename%20doesn't%20give%20local%20path%20to%20file%20in%20recent%20Excel%20release%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665374%22%20target%3D%22_blank%22%3E%40dannyg83%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDaniel%2C%20that%20can't%20be%20changed%2C%20if%20only%20Microsoft%20won't%20change%20the%20behaviour%20in%20one%20of%20new%20versions.%20For%20the%20files%20on%20synced%20with%20Sharepoint%2FOnedrive%20folders%20Excel%20returns%20web%20path.%20That's%20not%20only%20for%20the%20CELL()%2C%20everywhere.%20You%20may%20check%2C%20for%20example%2C%20File-%26gt%3BInfo-%26gt%3BCopy%20path%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

Hi, 

 

using this to get path and filename (=LEFT(@CELL("filename",C10),FIND("[",@CELL("filename",C10))-1)) only returns a SharePoint web address when the file is opened locally on a synced SharePoint.

It used to give me the local path which is needed in Power Query.

Why isn't this working anymore? It usually does, although it's a bit random at times. Reopening the file usually works in those cases.

But now it's always the SharePoint link.

How can this be changed?

 

Version is 2004 (Build 12730.20236)

 

Thanks

Daniel

 

1 Reply
Highlighted

@dannyg83 

Daniel, that can't be changed, if only Microsoft won't change the behaviour in one of new versions. For the files on synced with Sharepoint/Onedrive folders Excel returns web path. That's not only for the CELL(), everywhere. You may check, for example, File->Info->Copy path