Get file extension formula
 

Given file names that appear in a worksheet, and contain extensions of
uncertain length (e.g. .txt, .properties, .doc, etc), what's a good
formula to return the extension string itself?

Would want this to fail gracefully if there is no extension and handle
cases where "." appears elsewhere in the file name. That is, return the
last string delimited by "."

Thanks in advance!

Get file extension formula
 

=RIGHT(SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),LEN(SUBSTITUTE(A1,".", "~",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-FIND("~",SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"garle" wrote in message oups.com...
| Given file names that appear in a worksheet, and contain extensions of
| uncertain length (e.g. .txt, .properties, .doc, etc), what's a good
| formula to return the extension string itself?
|
| Would want this to fail gracefully if there is no extension and handle
| cases where "." appears elsewhere in the file name. That is, return the
| last string delimited by "."
|
| Thanks in advance!
|

Get file extension formula
 

To fail gracefully:

=IF(ISERROR(MID(A1,FIND("#",SUBSTITUTE(A1,".","#", LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),1)+1,200)),"",MID(A1,F IND("#",SUBSTITUTE(A1,".","#",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),1)+1,200))

HTH,
Bernie
MS Excel MVP


"garle" wrote in message
oups.com...
Given file names that appear in a worksheet, and contain extensions of
uncertain length (e.g. .txt, .properties, .doc, etc), what's a good
formula to return the extension string itself?

Would want this to fail gracefully if there is no extension and handle
cases where "." appears elsewhere in the file name. That is, return the
last string delimited by "."

Thanks in advance!