We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Trim characters from a cell reference

asked 2020-07-16 11:38:36 +0200

gtomorrow gravatar image

Hello again, all. I'm having a mental block today and was hoping someone could help out. I have a column in my spreadsheet that contains a path and filename (e.g, /home/grey/Documents/LOWriter/filename.ods). To be clear, this column contains text and not references/hyperlinks to the files. I would like to trim the text down to "filename.ods" in an adjacent column. What would be the correct function/formula to do so?

I know one of you geniuses know! ๐Ÿ˜† Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted

answered 2020-07-16 12:02:16 +0200

Opaque gravatar image

updated 2020-07-16 12:07:33 +0200


you may use: =REGEX(A1;"[^/]+(?=$)") assuming your filename is in cell A1.

Hop that helps.

edit flag offensive delete link more


The positive lookahead (?=$) is correct but not needed, the simple end anchor $ does as well, so =REGEX(A1;"[^/]+$")

erAck gravatar imageerAck ( 2020-07-16 12:10:22 +0200 )edit

@erAck - of course you are right. This solution was just a slight modification of a more general solution, which also allows for removing table names at the end of the filename using ...(?='#\$)

Opaque gravatar imageOpaque ( 2020-07-16 12:15:24 +0200 )edit

We have a WINNER! Thanks @Opaque and also @erAck for your speedy answer(s). What would I do without you? ๐Ÿ˜„ ๐Ÿ’Ÿ

gtomorrow gravatar imagegtomorrow ( 2020-07-16 12:29:26 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-16 11:38:36 +0200

Seen: 63 times

Last updated: Jul 16 '20