Ask Your Question

Calc : Function to get only filename

asked 2019-10-07 09:16:00 +0200

lonk gravatar image

I have used:


It is very long.

Can you please show yours shorter than this ?

edit retag flag offensive close merge delete


Would you mind to tell us what "filename" looks like?

Opaque gravatar imageOpaque ( 2019-10-07 09:43:56 +0200 )edit


Function: =CELL("filename")

Result: 'file:///home/fedora002/Documents/LibreOfficeCalcTesting/Filename1.ods'#$Sheet1

The result of such long function: Filename1.ods

lonk gravatar imagelonk ( 2019-10-07 10:03:31 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-10-07 15:16:48 +0200

erAck gravatar image

This matches the file name portion


unless your sheet name literally contains '#$.

edit flag offensive delete link more


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).

Opaque gravatar imageOpaque ( 2019-10-07 16:12:29 +0200 )edit


Thank you so much.

lonk gravatar imagelonk ( 2019-10-08 06:06:44 +0200 )edit

If you omit #\$ thus have only (?=') then if the sheet name contains ' the greedy [^/]+ match matches up to that position.

erAck gravatar imageerAck ( 2019-10-08 11:52:13 +0200 )edit

@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 ;-( )

Opaque gravatar imageOpaque ( 2019-10-08 12:02:03 +0200 )edit

The greediness of the regex can be fixed like this:


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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-08 14:28:17 +0200 )edit

Good observation :-)

erAck gravatar imageerAck ( 2019-10-09 13:37:48 +0200 )edit

answered 2019-10-07 10:34:48 +0200

Opaque gravatar image

updated 2019-10-07 11:54:44 +0200


my formula would look like


Note: For sure someone will be able to put everything into a single regular expression.

edit flag offensive delete link more



Thank you so much.

lonk gravatar imagelonk ( 2019-10-08 06:07:00 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-07 09:16:00 +0200

Seen: 54 times

Last updated: Oct 07