Excel Formula: excel extract file extension from filename
In some cases, you may want to extract the file extensions form a list of filenames in Excel worksheet. Here in this tutorial, introduces two different formulas to quickly handle this job, also you can realize how the formulas work in Excel.
Formula 1 Using SEARCH, RIGHT and REPLACE functions
Generic formula:
| REPLACE(RIGHT(filename,5),1,SEARCH(".",RIGHT(filename,5)),"") |
Arguments
| Filename: the filename you want to extract the extension. |
How this formula work
To extract the extension of filename in cell B3, please use the formula:
| =REPLACE(RIGHT(B3,5),1,SEARCH(".",RIGHT(B3,5)),"") |
Press Enter key to the cell B8.to extract the extension.
Explanation
RIGHT function is used to extract text from right side of a given text. Here RIGHT(B3,5) extracts 5 characters from right side of text in cell B3 since the longest length of extension is not longer than 5-digit. It returns “.xlsx”.
SEARCH function returns the location of the specific character or text from the given text string. Here SEARCH(".",RIGHT(B3,5)) finds the location of “.” in the text string “.xlsx” and returns 1.
REPLACE function finds and replaces characters based on given location from text string with a new text. REPLACE(RIGHT(B3,5),1,SEARCH(".",RIGHT(B3,5)),"") can be seen as REPLACE(".xlsx",1,1,""), it finds first character of the text string “.xlsx”, and replaces it with nothing.
Formula 2 Using LEN, RIGHT and FIND functions
Generic formula:
| RIGHT(filename,LEN(filename)-FIND(".",filename)) |
Arguments
| Filename: the filename you want to extract the extension. |
How this formula work
To extract the extension of filename in cell B3, please use the formula:
| =RIGHT(B3,LEN(B3)-FIND(".",B3)) |
Press Enter key to the cell B8.to extract the extension.
Explanation
FIND function returns the starting position of a string inside another one. FIND(".",B3) find the starting position of “.” in cell B3, it returns 15.
LEN function used to count the number of characters.
RIGHT function is used to extract text from right side of a given text. Here =RIGHT(B3,LEN(B3)-FIND(".",B3)) can be seen as RIGHT(B3,18-15), it extracts 3 characters from right side of the text in cell B3.
Sample File
Click to download sample fileRelative Formulas
- First letter lower case
Here introduces the formula to add new text to a certain position of the text string. - Trim Text To N Words
Here introduces the formula to extract n words from left side of a text string. - Add leaing zeros to fix length
This tutorial provides formulas to add leading zero to make the text at the same length. - Add dashes to phone number
To add dashes to phone number, you can use a formula to solve it in Excel.
Relative Functions
- RIGHT function
Extract text from right side. - REPLACE function
Find and replace characters based on given location from text string with a new text. - LEFT function
Extract characters from left side of text. - SEARCH function
Find the position of a specific character or substring from the given text string. - FIND function
Find a string within another string
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
