Excel VBA Substring: Extract, Find, and Replace Text

Excel VBA has no built-in function named Substring, but you can work with part of a string using Left$, Right$, and Mid$. Use InStr to locate text and Replace to substitute it; together, these functions cover most substring tasks in a macro.

What “substring” means in VBA

A substring is a portion of a longer string. For example, the text “INV-4827” contains “INV” at the beginning, “4827” at the end, and “482” within it. VBA handles these operations with built-in string functions rather than a method called Substring.

VBA positions are one-based: the first character is position 1, not position 0. That matters when you calculate a starting position for Mid$ or inspect the result from InStr. The dollar-sign forms, such as Mid$, return a String; the versions without the dollar sign return a Variant. In ordinary text-processing code, either form can work, but the dollar-sign versions make the intended type clear.

For example, if a string is “INV-4827”, position 1 is I, position 4 is the hyphen, and position 5 is 4. Once that numbering is clear, extraction and search operations become easier to reason about.

Extract text with Left$, Right$, and Mid$

Choose an extraction function based on where the desired characters are located:

Left$(text, count) returns the first count characters.
Right$(text, count) returns the last count characters.
Mid$(text, start, [count]) returns characters beginning at start. If you omit count, it returns the remaining characters through the end of the string.

For instance, Left$("INV-4827", 3) returns “INV”, while Right$("INV-4827", 4) returns “4827”. A call to Mid$("INV-4827", 5, 3) returns “482”. These functions return text; they do not change the original string.

Here is a complete procedure that extracts the four rightmost characters from a value and prints the result in the Immediate window of the Visual Basic Editor:

Option Explicit

Sub ShowTrailingCode()
    Dim sourceText As String
    Dim resultText As String

    ' Keep the example input in a string variable.
    sourceText = "INV-4827"
    resultText = Right$(sourceText, 4)

    ' View the result in the Visual Basic Editor's Immediate window.
    Debug.Print resultText
End Sub

The Immediate window displays 4827. To open it in the Visual Basic Editor, use View > Immediate Window. For text stored in a worksheet, read the cell value into a string variable first, or assign the extracted result directly to another cell.

Find a substring with InStr

InStr searches one string for another and returns the position where the match begins. If there is no match, it returns 0. For example, searching “INV-4827” for “-” returns 4. A common pattern is to find a separator first and then use Mid$ to extract the text after it.

This procedure handles both cases: it prints the portion after the first hyphen, or reports that no hyphen was found. The fourth argument makes the search explicitly binary, so the comparison is case-sensitive; the hyphen itself is unaffected by case.

Option Explicit

Sub ShowTextAfterHyphen()
    Dim sourceText As String
    Dim separatorPosition As Long
    Dim resultText As String

    ' Search the sample string for its first hyphen.
    sourceText = "INV-4827"
    separatorPosition = InStr(1, sourceText, "-", vbBinaryCompare)

    If separatorPosition > 0 Then
        resultText = Mid$(sourceText, separatorPosition + 1)
        Debug.Print resultText
    Else
        Debug.Print "No hyphen found"
    End If
End Sub

Adding 1 to the returned position starts the extraction just after the separator. Without that adjustment, the result would include the hyphen. For a case-insensitive search, pass vbTextCompare instead of vbBinaryCompare. You can also set comparison behavior with an Option Compare statement at the top of a module, but an explicit argument makes the choice visible at the individual search.

Replace matching text

Use Replace(expression, find, replacement) when you want a new string with matching text changed. For example, replacing “-” with “/” in “INV-4827” produces “INV/4827”. By default, the replacement is case-sensitive under VBA’s usual binary comparison behavior. You can pass vbTextCompare as the optional comparison argument when case should not matter.

Replace returns a new string; it does not edit a worksheet cell by itself. Assign the result back to a variable or to the cell whose value you want to change. This short procedure demonstrates the returned value:

Option Explicit

Sub ShowReplacement()
    Dim sourceText As String
    Dim resultText As String

    ' Replace every hyphen in the example string.
    sourceText = "INV-4827"
    resultText = Replace(sourceText, "-", "/", 1, -1, vbBinaryCompare)
    Debug.Print resultText
End Sub

The result is “INV/4827”. The optional count of -1 means replace every match. If you only need the first match, use a count of 1. Be aware that an empty search string is not a useful way to insert text between characters; use a deliberate loop or another approach if that is the goal.

Process substring values in worksheet cells

VBA string functions work on strings, so a macro typically reads a cell, searches or extracts a portion, and writes the result elsewhere. This example assumes the source text is in column A, row 1 is a header, and the extracted text should go in column B. It takes everything after the first hyphen and leaves column B blank when a row has no hyphen.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, so a backup is useful even when the procedure writes results into a separate column.

Option Explicit

Sub ExtractAfterHyphen()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim sourceText As String
    Dim separatorPosition As Long

    ' Use the active sheet; column A contains the source text.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Start at row 2 because row 1 is assumed to be a header.
    For rowNumber = 2 To lastRow
        sourceText = CStr(ws.Cells(rowNumber, "A").Value)
        separatorPosition = InStr(1, sourceText, "-", vbBinaryCompare)

        If separatorPosition > 0 Then
            ws.Cells(rowNumber, "B").Value = _
                Mid$(sourceText, separatorPosition + 1)
        Else
            ws.Cells(rowNumber, "B").ClearContents
        End If
    Next rowNumber
End Sub

The code finds the last used row in column A and processes each row from 2 through that row. If column A contains INV-4827, column B receives 4827. The CStr conversion makes the intent explicit when cell contents are read as text. If your data has numbers, error values, or formulas returning errors, decide how those cases should be handled before running a similar macro across a large range.

VBA substring functions versus worksheet formulas

Excel worksheet formulas and VBA have related but distinct tools. A worksheet formula changes or calculates a value in a cell as part of the workbook’s calculation system. VBA string functions run when a macro or procedure executes. Use worksheet formulas when the result should update automatically as source cells change; use VBA when you need to automate a multi-step task, apply conditional logic, or write results in bulk.

The names are not always identical. Worksheet formulas include functions such as LEFT, RIGHT, MID, FIND, SEARCH, and SUBSTITUTE. In VBA, the comparable string tools include Left$, Right$, Mid$, InStr, and Replace. Do not paste a VBA expression such as Mid$(text, 2, 3) into a worksheet cell and expect it to work as a formula: the dollar-sign VBA function is for code, not worksheet formula syntax.

For one-off text cleanup, formulas or Excel’s built-in data tools may be simpler than enabling macros. For repeated, specialized processing across many rows, VBA can avoid copying formulas and can combine extraction with other actions. Choose based on whether the result needs to recalculate automatically and whether the task benefits from automation.

Common problems and how to avoid them

Starting at zero: VBA string positions start at 1. A start position of 0 is not the way to refer to the first character.
Ignoring a failed search: InStr returns 0 when it finds no match. Check for a position greater than 0 before using that position to extract text.
Including the delimiter: To return text after a found separator, start at separatorPosition + 1. To keep the separator, start at separatorPosition.
Assuming searches ignore case: Binary comparison is case-sensitive. Choose vbTextCompare when case-insensitive matching is intended.
Confusing returned text with an edit: Functions such as Mid$ and Replace return a string. Assign that result to a variable or cell if you want to retain it.
Reading blank or non-text cells without a plan: Empty cells convert to empty strings, but error values can cause conversion problems. Check data quality or handle errors before processing a whole column.

Another useful distinction is between the Mid$ function and VBA’s Mid statement. The function reads part of a string and returns it. The statement can replace characters within a variable string at a specified position. It does not directly edit a worksheet cell unless you assign the modified variable back to that cell. For most extraction jobs, the function is the clearer choice.

Run VBA in Excel for Microsoft 365

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste a procedure into the module. If the Developer tab is visible, Developer > Visual Basic opens the editor as well. Run a procedure from the editor with the cursor inside it by pressing F5, or return to Excel and use Developer > Macros to select and run a macro. Save macro-enabled workbooks as an .xlsm file if you need to keep the VBA code.

On Excel for Mac, the Visual Basic Editor is available, although some Windows-specific VBA features and operating-system calls may not work unchanged. The usual editor shortcut is Option+F11; depending on the keyboard, you may need Fn+Option+F11. You can also open the editor from the Developer tab. The string functions shown here are standard VBA operations, but test the full workbook on the platform where it will be used. Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros.

Frequently Asked Questions

Does VBA have a Substring function?
No. VBA does not provide a built-in function named Substring. Use Left$, Right$, or Mid$ to extract a portion of a string.

How do I get everything after a character in VBA?
Find the character with InStr, check that the returned position is greater than zero, then call Mid$ starting one position later. This prevents an unsuccessful search from being treated as a valid location.

How do I check whether text contains another string?
Use InStr. A result greater than zero means the text was found; a result of zero means it was not. Choose vbTextCompare for case-insensitive matching or vbBinaryCompare for case-sensitive matching.

What is the difference between Mid and Mid$ in VBA?
Both retrieve characters from a string. Mid returns a Variant, while Mid$ returns a String. The dollar-sign version is often a clear choice when the value is definitely text.

Can a VBA macro change part of a cell’s text?
Yes. Read the cell value into a string, use a function such as Replace to create the changed text, and assign the result back to the cell. A macro that changes cells cannot be reversed with Ctrl+Z, so save a copy first.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.