Get file extension formula

[garle]

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!

 

[Niek Otten]

=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

| 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!
|

 

[Bernie Deitrick]

To fail gracefully:

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

HTH,
Bernie
MS Excel MVP