How to extract file extension from worksheet?

In today's digital age, where we handle a plethora of files and data, it is essential to efficiently organize and manage our information. One common task is extracting file extensions from a worksheet, which can be helpful for various purposes, such as categorizing or analysing data. In this article, we will guide you through the process of extracting file extensions from a worksheet using different software applications and programming languages. Let's get started!

To extract file extensions from cells, you can use the following formula −

Method 1: Extract file extension from worksheet with formula

Step 1

In an empty cell adjacent to your data, enter the formula "=REPLACE(RIGHT(A2,5),1,SEARCH(".",RIGHT(A2,5)),"")".

Here's a step−by−step breakdown of how this formula works−

a) RIGHT(A2,5)− This function extracts the rightmost characters from cell A2. You can adjust the number "5" based on the maximum length of your file extension.

b) SEARCH(".",RIGHT(A2,5))− This function searches for the position of the dot (.) within the extracted characters from step a.

c) REPLACE(RIGHT(A2,5),1,SEARCH(".",RIGHT(A2,5)),"")− This function replaces the extracted characters from step a with an empty string starting from the position of the dot (.) obtained in step b.

By applying this formula to the adjacent cells, you will extract the file extensions from your original data.

Note −

This formula assumes that the file extensions in your data have a maximum length of five characters. If your file extensions have a different length, adjust the number "5" in the formula accordingly.

Step 2

Next, you can use the fill handle to copy and paste the formula to the desired range, extracting file extensions from all the cells. Simply click and hold the fill handle (a small square at the bottom−right corner of the selected cell), and drag it over the range where you want the formula to be applied. This will automatically populate the formula in each cell, extracting the file extensions. Refer to the accompanying screenshot for visual guidance.

Method 2: Extract file extension from worksheet with User Defined Function

Here's an alternative approach that involves creating a User Defined Function−

• Press and hold the ALT + F11 keys to open the Microsoft Visual Basic for Applications (VBA) window.

• In the VBA window, click on Insert > Module. This will open a new module window.

• Copy and paste the following code into the module window −

Function FileExt(FileName As String) As String
'Update 20141111
   On Error Resume Next
   If InStrRev(FileName, ".") > 0 Then FileExt = Right(FileName, Len(FileName) − InStrRev(FileName, "."))
   If Err <> 0 Then MsgBox Err.Description, vbCritical, "Kutools for Excel"
End Function

• Close the VBA window to return to your Excel worksheet.

• In an empty cell, enter the formula =FileExt(A5) assuming the file path is in cell A2.

• Drag the fill handle across the range of cells where you want to extract the file extensions. The function will be applied to each cell, extracting the respective file extension.

By using this User Defined Function, you can easily extract file extensions from multiple cells without having to manually copy the formula to each cell.

Conclusion

Extracting file extensions from a worksheet can be a useful task when organizing and analysing data. In this article, we explored three different methods − using Microsoft Excel, Python, and Google Sheets. Whether you prefer a spreadsheet application or a programming language, these step-by-step guides will help you extract file extensions efficiently. With this knowledge, you can enhance your data management skills and streamline your workflows.