How to Extract File Extensions from Filenames in Excel

When working with file lists in Excel, you may need to identify file types by extracting their extensions, such as .xlsx, .docx, .pdf, or .jpg. This can be useful for organizing files, filtering specific file types, creating reports, or preparing data for further analysis. While extracting extensions manually from a few filenames is manageable, doing so for hundreds or thousands of filenames can be tedious and time-consuming.

In this tutorial, we'll show you three practical methods to extract file extensions from filenames in Excel: using a formula, a VBA function, and the powerful Extract Text feature of Kutools for Excel.

• For Excel 365, 2021 and later
• For Excel 2019, 2016 and earlier

Extract file extensions from filenames with a formula

If you prefer a built-in Excel solution, you can use a formula to extract file extensions directly from filenames. The formula you use depends on your Excel version.

For Excel 365, Excel 2021, and later versions

Modern Excel versions include the TEXTAFTER function, which makes extracting file extensions much easier.

Steps

1. Select a blank cell next to your filename list and enter the following formula:

   ="."&TEXTAFTER(A2,".",-1)

   Note: Replace A2 with the cell containing your filename.
2. Press Enter to get the result for the first filename.
3. Then drag the Fill Handle down to copy the formula to the remaining cells.

   

For Excel 2019, Excel 2016, and earlier versions

If your version of Excel does not support TEXTAFTER, you can use the following formula instead.

Steps

1. Select a blank cell next to your filename list and enter the following formula:

   =RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,".","@",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))+1)

   Note: Replace A2 with the cell containing your filename.
2. Press Enter to get the result for the first filename.
3. Then drag the Fill Handle down to copy the formula to the remaining cells.

   

Advantages of the formula methods

• No VBA required
• No additional tools required
• Suitable for quick extraction tasks
• Works well for most filename lists

However, formulas can become difficult to remember and maintain, especially when working with large datasets.

Extract file extensions from filenames with VBA

If you prefer using a custom function instead of a worksheet formula, you can extract file extensions with VBA. By creating a simple user-defined function, you can return the extension from a filename directly in a worksheet cell.

Steps

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module.
3. Copy and paste the following code into the module window:

   Function GetExtension(FileName As String) As String
       Dim Pos As Long
       Pos = InStrRev(FileName, ".")
       If Pos > 0 Then
           GetExtension = Mid(FileName, Pos)
       Else
           GetExtension = ""
       End If
   End Function

   

4. Close the VBA Editor and return to your worksheet.
5. In a blank cell, enter the following formula and press Enter.

   =GetExtension(A2)

6. Drag the Fill Handle down to apply the function to the remaining rows.
   The custom function extracts the file extension including the period as shown in the screenshot below.

   

Extract file extensions from filenames with Kutools for Excel

For the fastest and most user-friendly solution, Kutools for Excel provides the Extract Text feature, allowing you to extract file extensions without writing formulas or VBA code. With its rule-based extraction capability, you can process large filename lists in just a few clicks.

Steps

1. Click Kutools > Text > Extract Text.

   

2. In the Extract Text dialog box:
   1. Switch to the Extract by rule tab.
   2. Select the cells containing the filenames from which you want to extract extensions.
   3. Enter the following rule in the Text box:
      .*
3. Click Add to add the rule.
4. Click OK.

   

5. In the dialog box that appears, specify the output range and click OK.

   

Result

Kutools immediately extracts all file extensions, including the period (.), from the selected filenames and outputs them into the specified range.

Even large filename lists can be processed instantly without any formulas or manual editing.

---

Conclusion

Extracting file extensions can help you organize, analyze, and manage file-related data more efficiently. While formulas provide a quick worksheet-based solution and VBA offers a reusable custom function, both methods require either formula knowledge or coding skills.

If you're looking for the simplest and most efficient approach, Kutools for Excel's Extract Text feature is the best choice. Its rule-based extraction process eliminates the need for formulas and VBA, allowing you to extract file extensions from large filename lists quickly and accurately with just a few clicks.