return filename without extension

[andrewvanmarle]

Hi All!

years ago I got this code from a helpful user at this forum:

=(DEEL(CEL("bestandsnaam");VIND.SPEC("[";CEL("bestandsnaam"))+1;VIND.SPEC("]";CEL("bestandsnaam"))-VIND.SPEC("[";CEL("bestandsnaam"))-5))+C13

Which is supposed to return the 5 digit filename without the extension.

These days we sometimes have a letter appended to that five digit filename, and if we do this code returns a value error.

I've been fiddling with the +1 and the -5 but that didn't come up with the result i wanted.

Can anyone help?

 

[Dave Patton]

The following suggestion does not depend on the length of the file.
I cannot translate from English and you may have to replace , s with ;

The formula can be much shorter with Excel 365's Let function or a UDF.

=REPLACE(LEFT(CELL("filename",A1),FIND(".",CELL("filename",A1))-1),1,FIND("[",CELL("filename",A1)),"")

 

[andrewvanmarle]

Super!! thank you!

I translated it with Translator


to

=VERVANGEN(LINKS(CEL("bestandsnaam";A1);VIND.ALLES(".";CEL("bestandsnaam";A1))-1);1;VIND.ALLES("[";CEL("bestandsnaam";A1));"")

 

[Dave Patton]

Thanks for the feedback.

 

[andrewvanmarle]

you get a hero point from me: ?