| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determine Excel file version with no file extension. | Excel Discussion (Misc queries) | |||
| file format or file extension is not valid...error message | Excel Discussion (Misc queries) | |||
| Additional file with no extension created during File Save As proc | Excel Discussion (Misc queries) | |||
| how i open a file XLS extension[ exelspreadsheet file] | Excel Worksheet Functions | |||
| I need to download an exel spreadsheet file. (file extension :xls) | Excel Discussion (Misc queries) | |||
Linear Mode
