[Excel VBA] Two Ways to Get a Filename Without Extension (Split / InStrRev)

Introduction

When handling files in VBA, you frequently encounter situations where you need to remove the extension (e.g., .xlsx) from a full filename (e.g., Report.xlsx) to get just the base name (Report). This is common when creating new folders with the same name or outputting text files.

VBA offers two main approaches to achieve this: a “smart” one-liner and a “robust” method that handles complex filenames. This article explains both.

Method 1: Using the Split Function (Simple)

The Split function divides a string into an array based on a specified delimiter. Since filenames and extensions are separated by a period (.), this is the most intuitive method.

Code and Explanation

VBA

Sub GetBaseName_WithSplit()

    ' Declare variables
    Dim fullFileName As String
    Dim baseFileName As String
    
    ' Get the name of the currently active workbook
    fullFileName = ActiveWorkbook.Name
    
    ' Split the string by "." and get the first element (index 0)
    baseFileName = Split(fullFileName, ".")(0)
    
    ' Display the result
    MsgBox "Filename without extension: " & baseFileName

End Sub

Split(fullFileName, ".") breaks the string “Report.xlsx” into an array ("Report", "xlsx"). Since array indices start at 0, specifying (0) retrieves the part before the dot.

Caution: This method has a flaw. If the filename itself contains periods (e.g., My.Report.2025.xlsx), it will split at the first dot, returning only My.

Method 2: Combining InStrRev and Left (Robust)

This method combines InStrRev, which searches for a character from the end of the string, with Left, which extracts text from the beginning. This is the safer approach.

Code and Explanation

VBA

Sub GetBaseName_WithInStrRev()

    ' Declare variables
    Dim fullFileName As String
    Dim baseFileName As String
    Dim periodPosition As Long
    
    ' Get the name of the currently active workbook
    fullFileName = ActiveWorkbook.Name
    
    ' Find the position of the period starting from the END of the string
    periodPosition = InStrRev(fullFileName, ".")
    
    ' Process only if a period is found
    If periodPosition > 0 Then
        ' Extract everything to the left of the period
        baseFileName = Left(fullFileName, periodPosition - 1)
    Else
        ' If no period is found (file has no extension), use the original name
        baseFileName = fullFileName
    End If

    ' Display the result
    MsgBox "Filename without extension: " & baseFileName
    
End Sub

• InStrRev(fullFileName, “.”): Searches for the period starting from the right. This ensures that even if there are multiple periods, it finds the one separating the extension.
• Left(fullFileName, periodPosition – 1): Extracts the characters from the left up to (but not including) the period.

This method correctly handles filenames like My.Report.2025.xlsx, returning My.Report.2025.

Summary

Here is the comparison of the two methods: