Excel VBA Mid: How the Function Works

In Excel VBA, Mid returns part of a string, starting at a position you specify; positions begin at 1, not 0. For example, Mid$("AB-48392", 4, 4) returns 4839. Use it to extract characters from text in a macro, and use the similar worksheet function when you need the result directly in a cell.

What Mid Does in Excel VBA

Mid is a built-in Visual Basic for Applications (VBA) string function. It takes text, a starting character position, and optionally the number of characters to return. It does not search for a word or delimiter: it simply counts from the beginning of the string and returns the requested slice.

This makes it useful when text has a consistent layout. You might extract a two-character region code from the start of an identifier, or retrieve a four-digit year from a code whose year always occupies the same positions. If the text varies in length or the part you want moves around, searching for a separator with a function such as InStr may be more appropriate.

VBA also has Mid$. It performs the same extraction but returns a String rather than a Variant. In ordinary code, either form is suitable when you want text; examples often use Mid$ to make the intended return type explicit.

Syntax and Arguments

The function syntax is Mid(string, start, [length]). The square brackets mean that length is optional; do not type the brackets themselves. In VBA, commas separate arguments.

string: the text value to read. It can be a string variable, a literal, or a value obtained from a cell.
start: the position of the first character to return. The first character is position 1.
length: how many characters to return. If omitted, the function returns from the starting position through the end of the string.

If the requested length extends beyond the end of the text, Mid returns the characters that remain. If the starting position is greater than the string length, the result is an empty string. A start position of zero or less, or a negative length, is invalid and can raise a run-time error. For code that receives uncertain positions, check them before calling the function.

How Character Positions Work

Positions are counted from the left edge of the string, beginning with 1. They are not the same as a worksheet column number, a character code, or an index that starts at zero in some programming languages. Spaces and punctuation count as characters, too.

For example, in AB-48392, the hyphen is character 3, and the first digit is character 4. Starting at 4 and taking four characters gives 4839. Starting at 3 instead would include the hyphen and return -483. When an extraction looks off by one character, writing the source text out with numbered positions is a quick way to find the problem.

The function returns text, even when the extracted characters look like a number. If you need to calculate with the result, convert it to a numeric type deliberately and account for possible nonnumeric characters. Keeping identifiers as text is often preferable: converting values can remove leading zeros, which may be significant in product codes, account references, or postal codes.

Mid Function Versus the Mid Statement

VBA has both a Mid function and a Mid statement. The function reads text and returns a new string value. The statement uses an assignment form to replace characters inside an existing string variable. They share a name but serve different purposes.

For example, if a variable contains Cat, assigning u at position 2 changes it to Cut. This replacement does not insert a character or shift the later characters along; it changes the existing text at the specified position. Use the statement for a fixed-position edit, not when you want to build a longer string by inserting text.

Option Explicit

Sub ReplaceSecondCharacter()
    Dim animal As String

    animal = "Cat"
    ' Replace one existing character; this does not insert a character.
    Mid(animal, 2, 1) = "u"
    MsgBox animal
End Sub

Before running a macro, save a copy of the workbook. Macro changes cannot be undone with Ctrl+Z. The example displays Cut; it does not change a worksheet cell.

Practical VBA Examples

A direct call is useful when you already have a string in a variable. This procedure extracts four characters from position 4. It illustrates the function, not the statement: the result is assigned to a separate variable.

Option Explicit

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

    sourceText = "AB-48392"
    resultText = Mid$(sourceText, 4, 4)
    MsgBox resultText  ' Displays 4839.
End Sub

To read a worksheet value, pass the cell’s value to Mid$. This example assumes that A2 contains text and puts the extracted characters in B2. It also assumes A2 is not an Excel error value, because converting an error cell with CStr can cause a type mismatch.

Option Explicit

Sub ExtractFromOneCell()
    Dim ws As Worksheet
    Dim sourceText As String

    Set ws = ActiveSheet
    sourceText = CStr(ws.Range("A2").Value)
    ws.Range("B2").Value = Mid$(sourceText, 4, 4)
End Sub

For a list, process each row and write the output to a neighboring column. This version expects a header in row 1, text values in column A from row 2 onward, and no error values in those cells. If there is no data below the header, the loop does not run.

Option Explicit

Sub ExtractFromColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim sourceText As String

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For rowNum = 2 To lastRow
        sourceText = CStr(ws.Cells(rowNum, "A").Value)
        ' Take four characters beginning at position 4.
        ws.Cells(rowNum, "B").Value = Mid$(sourceText, 4, 4)
    Next rowNum
End Sub

Save a copy before running either macro, because VBA edits are not reversed by Ctrl+Z. The loop also assumes the source text follows a consistent pattern. If some rows use a different layout, add checks for those cases rather than assuming the same character positions always mean the same thing.

VBA Mid and the Excel MID Worksheet Function

Excel’s worksheet function is named MID and follows the same basic counting rule: it starts at position 1 and returns a specified number of characters. It is convenient when the extraction belongs in a cell formula and does not require a macro. In Excel for Microsoft 365 on Windows, enter the formula in a cell and fill it down as needed.

For example, with the text AB-48392 in A2, the formula =MID(A2,4,4) returns 4839. The VBA equivalent for a string variable is Mid$(sourceText, 4, 4). Use the worksheet function for a formula-driven result; use VBA when the extraction is part of a larger automated task, such as processing cells and writing results in one procedure.

Do not confuse the worksheet function with the VBA function just because they have the same name. A formula entered in a worksheet uses worksheet syntax, while code in a VBA procedure uses VBA syntax and variables. For one-off extraction, a formula may be simpler; for repeatable workbook automation, VBA may fit better.

Choosing Mid, Left, Right, or InStr

Choose the string tool that matches the structure of your data. Fixed positions suit Mid; text at the beginning or end may be simpler with Left or Right. When a separator marks the boundary, find its location rather than guessing that the desired text always starts at a fixed position.

Use Left when you need a known number of characters from the beginning.
Use Right when you need a known number of characters from the end.
Use Mid when the starting position is known or calculated and you need a section of the string.
Use InStr when you need to find where a particular character or piece of text appears, then use that position to guide extraction.

Suppose a code is always Region-Item, but the region length varies. A hard-coded start position for the item is fragile. Find the hyphen with InStr, then calculate the position after it. If a value can contain more than one hyphen, decide which one marks the boundary and handle that rule explicitly. The right approach depends on the data format, not simply on which function is shortest.

Common Problems and How to Avoid Them

Off-by-one positions: VBA starts counting at 1. Include spaces and punctuation when counting characters.
Missing or short text: An omitted length returns the remaining text, and a request extending past the end returns only what remains. Check the result against the expected format.
Invalid arguments: A start value below 1 or a negative length can cause a run-time error. Validate positions that come from user input or calculations.
Unexpected numeric conversions: Extracted digits are text. Convert only when arithmetic is required, and preserve identifiers as text if leading zeros matter.
Cells containing errors: A worksheet error value is not ordinary text. Check for errors before converting a cell value or passing it into string operations.
Changing formats: If text does not have a reliable fixed layout, a fixed position may return the wrong segment without raising an error. Validate inputs or locate a delimiter.

Also remember that extracting characters does not remove spaces automatically. If a result may have unwanted spaces at its edges, use a suitable trimming approach after extraction, while taking care not to remove spaces that are meaningful in the source.

Excel Version, Macros, and Safety

VBA is available in the desktop versions of Excel for Windows and Mac, but Excel for the web cannot create, edit, or run VBA macros. On Windows, open the VBA editor with Alt+F11, or use the Developer tab’s Visual Basic command. On a Mac, the editor shortcut is commonly Option+F11; some keyboards require Fn as well. The Visual Basic command is also available from the Developer tab in desktop Excel for Mac.

If you need the Developer tab on Windows, use File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Settings (or Preferences, depending on the release) > Ribbon & Toolbar and enable Developer. Menu wording can vary slightly across releases. A workbook containing VBA generally needs to be saved as an Excel Macro-Enabled Workbook (.xlsm) to retain its macros.

Only enable macros in files from sources you trust. A macro can change workbook content, and running it may have effects that are not easy to reverse. Keep a backup copy before running code, especially when a procedure edits cells or loops through a large range. For a simple extraction that needs no automation, a worksheet formula avoids the need to run a macro.

Frequently Asked Questions

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

What happens if I leave out the length argument?
Mid returns the text from the starting position through the end of the string. If the start is beyond the end, the result is an empty string.

What is the difference between Mid and Mid$?
Both extract a substring. Mid returns a Variant containing text, while Mid$ returns a String. For typical text extraction, their visible results are the same.

Can Mid replace text in a variable?
The Mid statement can replace characters in an existing string variable using assignment syntax. It changes characters in place rather than inserting text or shifting the rest of the string.

Can I use VBA Mid in Excel for the web?
No. Excel for the web does not run VBA macros. Use the worksheet MID function there, or open the workbook in desktop Excel to use VBA.

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.