Skip to content

Instantly share code, notes, and snippets.

@dznz

dznz/gist:64a34466f44d1a335e75

Created October 29, 2015 22:40
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save dznz/64a34466f44d1a335e75 to your computer and use it in GitHub Desktop.
Save dznz/64a34466f44d1a335e75 to your computer and use it in GitHub Desktop.
Download ZIP
Extract the filename from a path in Excel or Google spreadsheet
Raw
gistfile1.txt
=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),99))
@assainov
Copy link

Thank you

@eiannone
Copy link

eiannone commented May 31, 2019 •
edited

Clever! But does not work if the filename has more than 100 characters. You could use:
=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)-1))

@3rdAnimal
Copy link

Thanks for the tip. Also, you can trim the file extension with this:
=TRIM(RIGHT(SUBSTITUTE(Left(A1, Find(".",A1)-1),"/",REPT(" ",100)),99))

@verkestk
Copy link

riffing on @eiannone's response:

=IFERROR(IF(SEARCH("/",A1,1), TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)-1))), A1)

Wrapping the TRIM in a conditional that looks for the / character - otherwise the first character gets trimmed improperly.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment