I have used:
=RIGHT(LEFT(CELL(“filename”),(SEARCH(".ods’",CELL(“filename”),1)+3)),LEN(LEFT(CELL(“filename”),(SEARCH(".ods’",CELL(“filename”),1)+3)))-FIND("@",SUBSTITUTE(LEFT(CELL(“filename”),(SEARCH(".ods’",CELL(“filename”),1)+3)),"/","@",LEN(LEFT(CELL(“filename”),(SEARCH(".ods’",CELL(“filename”),1)+3)))-LEN(SUBSTITUTE(LEFT(CELL(“filename”),(SEARCH(".ods’",CELL(“filename”),1)+3)),"/",""))),1))
It is very long.
Can you please show yours shorter than this ?
Would you mind to tell us what “filename” looks like?
Function: =CELL(“filename”)
Result:
‘file:///home/fedora002/Documents/LibreOfficeCalcTesting/Filename1.ods’#$Sheet1
The result of such long function: Filename1.ods
This matches the file name portion
=REGEX(CELL("filename");"[^/]+(?='#\$)")
unless your sheet name literally contains '#$.
Really great - but could you explain, just to satisfy my curiosity, why #\$ are required in look ahead (?=...) (if I leave it off it looks still working).
If you omit #\$ thus have only (?=') then if the sheet name contains ' the greedy [^/]+ match matches up to that position.
@erAck Thanks - now I finally understand your unless your sheet name literally contains '#$ restriction and my bias ( My bias interpreted "unless contains any of ', # or $ - I’m embarrassed ;-( )
The greediness of the regex can be fixed like this:
=REGEX(CELL("filename");"[^/#]+(?='#\$)")
Rationale:CELL(“filename”) returns an URI; in the URI, the # is a reserved character that separates fragment; so if this character happens to appear in filename, it would be encoded as %23, so it’s safe to disallow that character in the filename part. Then the search will stop at the first encounter of '#\$, and not on possible following occurrences in sheet name.
Good observation 
To get the file name without the extension, I just replaced the (?=’#$) with (?=.) and that worked for me
Hello,
my formula would look like
=REGEX(REGEX(CELL("filename"),".*/","","g"),"'#.*","","g")
Note: For sure someone will be able to put everything into a single regular expression.
My file is a .xlsx
This is what I use to to return the filename without the “.xlsx” extension. You can just modify by changing the extension you want to remove.
I tried the previous solutions, but I was getting the extension in the end and I didn’t want that.
This is the function shown below
=REGEX(REGEX(CELL(“filename”),"[^/]+(?=’#$)"),".xlsx","",“g”)
Suggest:
=CELL(“FILENAME”,$A$1)
will do. Empty cell is better.
It doesn’t matter for the function with "FILENAME" argument. But your “answer” is not an answer/solution to the question/problem and should had been a comment instead.
You can write your own custom Cell function in the macro editor. Then you will able to call it by name.
The code uses the built-in Library “Tools”, what you mus load into memory before you use it.
In my sample code there is hints to separate the filename and its extension - if it needed.
REM ***** BASIC *****
Function GetFileName()
'Load the built-in "Tools" library, what contains the functions used below
if (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
GlobalScope.BasicLibraries.LoadLibrary("Tools")
end if
sURL = ThisComponent.URL
sFnameAndExt = FileNameoutofPath(sURL)
'How to separate the filename and the extension if it needed:
'sFileName = GetFileNameWithoutExtension(sFnameAndExt)
'sFileExt = GetFileNameExtension(sFnameAndExt)
'GetFileName = sFileName & "." & sFileExt
GetFileName = sFnameAndExt
End function
GetFileName.ods (9.6 KB)
You can make this code more “foolprof” by examining if the document has an URL, or it is an unsaved new document yet.