Ask Your Question
0

Calc : Function to get only filename

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

lonk gravatar image

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 ?

edit retag flag offensive close merge delete

Comments

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

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

@Opaque

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
1

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

erAck gravatar image

This matches the file name portion

=REGEX(CELL("filename");"[^/]+(?='#\$)")

unless your sheet name literally contains '#$.

edit flag offensive delete link more

Comments

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

@erAck@Opaque

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
1

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.

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
1

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

Opaque gravatar image

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

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.

edit flag offensive delete link more

Comments

@Opaque

Thank you so much.

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

Question Tools

1 follower

Stats

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

Seen: 54 times

Last updated: Oct 07