Excel VBA Mid Function: Syntax, Examples, and Tips

VBA & macros · Updated November 21, 2026

In Excel VBA, the Mid function returns part of a string, starting at a position you specify. Use Mid(text, start, length) to extract a set number of characters, or leave out length to return everything from the starting position to the end.

What the VBA Mid Function Does

Mid is a text function in Visual Basic for Applications (VBA), Excel’s macro programming language. It selects characters from a string by position; it does not search for a word or delimiter. For instance, the sixth character in Northwind is “w,” so Mid("Northwind", 6, 4) returns wind.

That position-based behavior is useful when text follows a predictable layout: a code may always put a department identifier in characters 2 through 4, or a legacy export may store a date in a fixed-width field. If the characters you need can appear at different positions, use a search function such as InStr to find the position first, then pass that position to Mid.

Syntax and Arguments

The VBA function syntax is:

Mid(string, start, [length])

  • string is the text to read. It can be a string variable, a string literal, or a value that VBA can convert to text.
  • start is the character position where extraction begins. Positions are one-based: the first character is position 1.
  • length is optional and specifies how many characters to return. If omitted, the function returns the remaining characters through the end of the string.

The result of Mid is a string value (the function’s declared return type is a Variant containing a String). VBA also provides Mid$, which returns a String directly. For ordinary extraction, either is commonly used; Mid is often easier to recognize because it matches the function name used in explanations and examples.

How Start Positions and Lengths Work

VBA counts from 1, not 0. In the text Excel VBA, the first character is E, the sixth is a space, and the seventh is V. Thus, a start position of 7 begins at “V.” A length of 3 returns three characters if that many remain.

If length is omitted, Mid returns the text from start to the end. If the requested length extends past the end, the result is simply the characters that remain. A start position greater than the string’s length produces a zero-length string. A start value below 1, or a negative length, causes an invalid procedure call error. A length of zero returns an empty string.

For example, with text set to Excel VBA, the call Mid(text, 7, 3) returns VBA, while Mid(text, 7) also returns VBA because there are exactly three characters left. These rules make it worth checking the input length when positions come from user data or calculations.

Practical VBA Examples

These short procedures write results to the Immediate window in the Visual Basic Editor. Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Sub ExtractExample()
    Dim text As String
    Dim result As String

    text = "Northwind"
    result = Mid(text, 6, 4) ' Take four characters from position 6.
    Debug.Print result       ' Prints: wind
End Sub

The second argument is the starting position and the third is the number of characters. To take the rest of a value instead, omit the third argument:

Sub RestOfTextExample()
    Dim text As String
    Dim result As String

    text = "Order-4582"
    result = Mid(text, 7) ' Take the text from position 7 to the end.
    Debug.Print result    ' Prints: 4582
End Sub

To see output, open the Visual Basic Editor and show its Immediate window (View > Immediate Window). On Windows, Alt+F11 opens the editor; on Mac, Option+F11 commonly does, though some keyboards also require Fn. Excel for the web can open a workbook containing macros, but it cannot run VBA macros.

Using Mid with Fixed-Width Text

Suppose an imported record always contains a three-character region code, followed by a five-character product code and then a quantity. You can extract fields by their fixed positions. Here, the example record is NEAAB1230007: positions 1–3 are the region, positions 4–8 are the product code, and positions 9–12 are the quantity.

Sub ReadFixedWidthRecord()
    Dim record As String
    Dim region As String
    Dim productCode As String
    Dim quantityText As String

    record = "NEAAB1230007"
    region = Mid(record, 1, 3)       ' NEA
    productCode = Mid(record, 4, 5)  ' AB123
    quantityText = Mid(record, 9, 4) ' 0007

    Debug.Print region, productCode, quantityText
End Sub

Notice that the quantity stays text in this example, preserving its leading zeroes. Convert it to a number only if arithmetic is needed and the zeros are not meaningful. Fixed positions are dependable only while the source layout stays fixed; if fields have variable lengths or separators, searching for delimiters is usually safer.

Mid Compared with Other Text Tools

Choose the text operation that matches the task. Left returns characters from the beginning, and Right returns characters from the end. Mid starts at a position you provide, which may be in the middle or elsewhere in the string. For example, use Left(text, 3) for the first three characters and Right(text, 4) for the last four.

InStr is different: it searches for a substring and returns its position. You can combine it with Mid when a value follows a marker rather than occupying a known position. For example, find the position of a colon with InStr(text, ":"), then begin extraction one character after the colon. Check that the colon was found before using the returned position; when it is not found, InStr returns 0, which is not a valid starting position for Mid.

There is also a worksheet function named MID, used in cell formulas, for example =MID(A2,6,4). It performs the same basic positional extraction but follows worksheet formula conventions rather than VBA syntax. In VBA code, call Mid as a function; in a worksheet cell, enter MID as part of a formula.

Mid Function Versus the Mid Statement

A frequent source of confusion is that VBA has both a Mid function and a Mid statement. The function reads part of a string and returns it. The statement replaces characters inside an existing string variable. It is an assignment operation, so it does not return a substring.

Sub ReplaceCharactersExample()
    Dim status As String

    status = "New pending"
    Mid(status, 1, 3) = "Old" ' Replace the first three characters.
    Debug.Print status       ' Prints: Old pending
End Sub

The statement changes the variable in place; it does not insert text and make the string longer. Use it only when editing a string at known character positions is intentional. If you only want a portion of text for display or further processing, assign the result of the Mid function to a separate variable instead.

Common Problems and How to Avoid Them

  • Using zero-based positions: VBA starts at 1. If a character is described as the sixth character, pass 6—not 5.
  • Expecting a search: Mid does not locate words. Find a changing position with InStr first, and check the search result before extracting.
  • Requesting an invalid start or length: A start below 1 or a negative length raises an error. Validate positions when they are calculated from external input.
  • Assuming short input is an error: If the requested length runs beyond the end, VBA returns the remaining characters. If the start is beyond the end, it returns an empty string.
  • Confusing characters with bytes: Mid works with string character positions, not byte offsets. For binary data or byte-oriented encodings, use byte functions such as MidB only when that byte-level behavior is appropriate.
  • Expecting Ctrl+Z to undo a macro: Excel’s undo history does not reverse VBA changes. Save a backup before running code that edits workbook content.

Also remember that a position valid for one record may not be valid for another. If a macro processes a range of imported values, consider checking each string’s length before extracting fields. For data with separators, trimming inconsistent spaces and locating delimiters is often more reliable than relying on hard-coded character numbers.

Frequently Asked Questions

Does VBA Mid start counting at 0 or 1?
It starts at 1. The first character in a string is position 1, so a start value of 1 begins at the first character.

What happens if I leave out the length?
Mid(text, start) returns all characters from the specified start position to the end of the string.

What is the difference between Mid and Mid$?
Both extract text. Mid returns a Variant containing a String, while Mid$ returns a String. The dollar-sign form can make the intended string type explicit; it is not the statement that edits a variable.

Can I use Mid in an Excel cell formula?
Yes. Enter the worksheet function as =MID(A2,6,4), assuming the text is in A2. In VBA code, use the function without the leading equals sign, such as Mid(text, 6, 4).

Why does Mid return an empty string?
A start position greater than the string’s length returns an empty string. A zero length also returns an empty string. Check the text length and the values passed to the start and length arguments if that result is unexpected.

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.