VBA Mid, Left, and Right: How to Extract Text

VBA & macros · Updated November 11, 2026

In VBA, Left returns characters from the start of a string, Right returns characters from its end, and Mid returns characters beginning at a position you specify. Use them to extract pieces of text, such as a code prefix, year, or suffix; Mid also has a separate statement form that can replace characters in a string variable.

What Left, Mid, and Right Do

These are built-in VBA string functions. They work on text values and return the requested portion without changing the original string. VBA counts character positions starting at 1, not 0. That one-based numbering is important when you choose the starting position for Mid.

  • Left takes characters from the beginning of a string.
  • Right takes characters from the end of a string.
  • Mid takes characters from a specified starting position, optionally limited to a specified number.

For example, given INV-2026-048, Left can return the three-letter prefix INV, Mid can return the year 2026, and Right can return the final three digits 048. These functions are particularly useful when a text value follows a consistent layout.

Syntax and Position Counting

The function syntax is Left(string, length), Right(string, length), and Mid(string, start, [length]). The brackets around length in the Mid syntax mean that argument is optional; do not type the brackets into your code.

For Left and Right, string is the text to read and length is how many characters to return. For Mid, start is the position of the first character to return. If you omit its optional length, Mid returns all characters from the starting position through the end of the string.

For example, in the string INV-2026-048, the first character is at position 1, the hyphen is at position 4, and the year starts at position 5. When a requested length is longer than the available text, Left and Right return the characters that are available. If a Mid start position is beyond the end of the string, it returns an empty string. A negative length or start position is not a useful way to handle missing data and can cause a run-time error.

Basic Examples in VBA

The following procedure prints four results in the Visual Basic Editor’s Immediate window. In the editor, open the Immediate window with Ctrl+G on Windows if it is not visible.

Sub DemonstrateTextFunctions()
    Dim sourceText As String

    ' Set a sample identifier and print selected sections.
    sourceText = "INV-2026-048"
    Debug.Print Left$(sourceText, 3)
    Debug.Print Mid$(sourceText, 5, 4)
    Debug.Print Right$(sourceText, 3)
    Debug.Print Mid$(sourceText, 5)
End Sub

The lines print INV, 2026, 048, and 2026-048, respectively. The last line shows how omitting the length argument makes Mid return the remainder of the string. The examples use the dollar-suffixed forms, Left$ and Mid$. These return a String; the unsuffixed Left, Mid, and Right forms return a Variant containing a string for ordinary text input. The unsuffixed forms are also common and easy to read.

Extracting Text Around a Delimiter

A fixed starting position is suitable only when the text layout is reliably fixed. If a delimiter such as a colon can appear at different positions, find it first with InStr, then use Mid to take the text after it. This avoids assuming the delimiter is always in the same place.

Sub GetTextAfterColon()
    Dim sourceText As String
    Dim colonPosition As Long
    Dim resultText As String

    ' Find the colon, then take the text after it.
    sourceText = "Status:Approved"
    colonPosition = InStr(1, sourceText, ":", vbBinaryCompare)

    If colonPosition > 0 Then
        resultText = Mid$(sourceText, colonPosition + 1)
    Else
        resultText = ""
    End If

    Debug.Print resultText
End Sub

This prints Approved. InStr returns 0 when it does not find the delimiter, so the check prevents the code from treating a missing colon as a valid position. If the text after the delimiter may include leading spaces, remove them separately with Trim$ when that matches your requirements; do not trim automatically if spaces are meaningful.

Changing Text with the Mid Statement

There is an important distinction between the Mid function and the Mid statement. The function extracts text into an expression. The statement assigns text into a section of an existing string variable. It is written with the assignment on the right of the Mid statement, as in this example:

Sub ReplaceCharacters()
    Dim codeText As String

    ' Replace four characters in the middle of the variable.
    codeText = "AB-1234"
    Mid(codeText, 4, 4) = "9876"
    Debug.Print codeText
End Sub

The result is AB-9876. The target must be a variable that can be changed; you cannot use the statement to edit a fixed literal or to change text inside a worksheet cell directly. If the replacement text is shorter than the target section, the remaining characters in that section are not automatically removed. If you need to change a cell, read its value into a variable, edit the variable, and write the result back to the cell, or use a worksheet formula or another appropriate method.

Choosing the Right Function

Choose based on where the useful text sits and whether its position is predictable:

  • Use Left for a known prefix, such as the first two letters of a department code.
  • Use Right for a known suffix, such as the final four characters of a reference.
  • Use Mid for a known section inside a string, such as a year in a fixed-format identifier.
  • Use InStr with Mid when the section is identified by a delimiter rather than a fixed position.

If your source values may have different layouts, extracting by fixed character counts can silently return the wrong part even when the code runs. For example, taking the last four characters of a reference works only if every reference ends with a four-character field. Check the format first, and decide what should happen when a value is blank, incomplete, or missing its expected delimiter.

Common Problems and Edge Cases

Many mistakes with these functions come from counting positions incorrectly or assuming that a string has a standard layout. Keep these points in mind:

  • VBA starts counting at 1. A start position of 1 means the first character. Do not use 0 to mean the first character.
  • The requested length is a character count. For Right(text, 3), the 3 means three characters from the end, not the character at position 3.
  • Spaces are characters. A leading space affects positions and can appear in the returned result. Use trimming only if removing spaces is appropriate for the data.
  • Empty strings need deliberate handling. Extracting from an empty string generally produces an empty string for valid nonnegative requests, but code should still account for blank inputs when the result affects later logic.
  • Lengths and positions must be valid. Avoid negative values. Validate values calculated from user input or a search before passing them to a string function.

For instance, if you expect a separator and use InStr to locate it, test whether the returned position is greater than 0 before calculating a substring. If you use a fixed layout, verify the input’s length with Len before assuming all requested characters are present. These checks make the code’s behavior explicit instead of relying on accidental input consistency.

VBA Functions Versus Excel Worksheet Functions

VBA’s Left, Mid, and Right are similar in purpose to Excel worksheet functions, but their syntax depends on where you write them. In a worksheet cell, use formulas such as =LEFT(A1,3) or =MID(A1,5,4). In a VBA procedure, refer to a variable or expression, as in Left$(sourceText, 3). A worksheet formula starts with an equals sign; a VBA statement does not.

Use worksheet formulas when the extraction should update automatically as cell values change and you do not need a macro. Use VBA when the operation belongs in a larger automated process, such as preparing values before exporting a report. Excel for the web can display a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop Excel app for those tasks.

Running and Saving a VBA Example

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste a procedure into the module, and run it with the cursor inside the procedure by pressing F5. The results from Debug.Print appear in the Immediate window. On Mac, open the Visual Basic Editor from Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the Mac’s function-key settings.

Save a copy of the workbook before running a macro that changes workbook data, and save a macro-enabled workbook as an .xlsm file if it needs to retain VBA code. Macro changes cannot be undone with Ctrl+Z in Excel, so keeping a backup is a practical safeguard.

Frequently Asked Questions

Does VBA Mid start at zero or one?
It starts at 1. In Mid$(text, 1, 1), the function returns the first character. A start position of 0 is invalid for normal use and can cause an error.

How do I get everything after a character in VBA?
Find the character with InStr, check that the result is greater than 0, and pass the position after it to Mid. Omitting the length returns the rest of the string.

What happens if the requested length is longer than the text?
Left and Right return the available characters rather than adding padding. For reliable processing, check the input length when your code requires a complete field.

Can Mid replace characters instead of extracting them?
Yes. The Mid statement assigns replacement text into a specified section of a string variable. That is different from the Mid function, which returns a substring.

Should I use Left or Left$ in VBA?
For ordinary text, either is commonly used. Left returns a Variant, while Left$ returns a String. The dollar-suffixed form can be useful when you specifically want a String result; the same distinction applies to Mid/Mid$ and Right/Right$.

Related

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.