Get file extension formula

  • Thread starter Thread starter garle
  • Start date Start date
G

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!
 
=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!
|
 
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
 
Back
Top