Home

Insert File name via an array formula

%3CLINGO-SUB%20id%3D%22lingo-sub-568351%22%20slang%3D%22en-US%22%3EInsert%20File%20name%20via%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568351%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20just%20discovered%20this%20tool%2C%20fantastic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBUT%2C%20can%20the%20field%20be%20amended%20to%20remove%20the%20file%20extension%3F%26nbsp%3B%20I%20want%20to%20use%20the%20field%20within%20a%20report%20as%20the%20document%20reference%2C%20but%20without%20the%20.xlsx%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-568351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-568378%22%20slang%3D%22en-US%22%3ERe%3A%20Insert%20File%20name%20via%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342288%22%20target%3D%22_blank%22%3E%40SuzanH%3C%2FA%3E%26nbsp%3B%2C%20do%20you%20mean%20this%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-ie%2Farticle%2Finsert-the-current-excel-file-name-path-or-worksheet-in-a-cell-186833c6-c899-4912-a14c-240c2eb51e0b%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-ie%2Farticle%2Finsert-the-current-excel-file-name-path-or-worksheet-in-a-cell-186833c6-c899-4912-a14c-240c2eb51e0b%3C%2FA%3E%26nbsp%3Bformula%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20returns%20something%20like%20path%5C%5Bfilename.xlsx%5DSheet1%20and%20you'd%20like%26nbsp%3Bpath%5C%5Bfilename%5DSheet1%2C%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572475%22%20slang%3D%22en-US%22%3ERe%3A%20Insert%20File%20name%20via%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572475%22%20slang%3D%22en-US%22%3EThis%20formula%20returns%20the%20file%20name%20without%20the%20file%20extension%3A%3CBR%20%2F%3E%3DMID(CELL(%22filename%22%2CA1)%2C%3CBR%20%2F%3EFIND(%22%5B%22%2CCELL(%22filename%22%2CA1))%2B1%2C%3CBR%20%2F%3EFIND(%22.x%22%2CCELL(%22filename%22%2CA1))-FIND(%22%5B%22%2CCELL(%22filename%22%2CA1))-1)%3C%2FLINGO-BODY%3E
SuzanH
Occasional Visitor

I've just discovered this tool, fantastic.

 

BUT, can the field be amended to remove the file extension?  I want to use the field within a report as the document reference, but without the .xlsx

Thanks

2 Replies

@SuzanH , do you mean this https://support.office.com/en-ie/article/insert-the-current-excel-file-name-path-or-worksheet-in-a-c... formula?

 

It returns something like path\[filename.xlsx]Sheet1 and you'd like path\[filename]Sheet1, correct?

This formula returns the file name without the file extension:
=MID(CELL("filename",A1),
FIND("[",CELL("filename",A1))+1,
FIND(".x",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies