In Excel VBA, use Mid(text, start[, length]) to return characters from the middle of a string; positions start at 1, and the optional length says how many characters to return. To replace characters inside a string variable, use the separate VBA statement form, Mid(textVariable, start[, length]) = replacement.
What Mid does in VBA
Mid is a built-in VBA function for working with part of a string. It is useful when a value has a predictable structure, such as an order code with a year in fixed positions, or when you need to take a piece of a longer description. For example, from the text INV-2026-047, you can retrieve the four characters that make up the year.
VBA also provides a Mid statement that changes characters in an existing string variable. The two forms share a name but do different jobs: the function returns a string, while the statement assigns replacement text into part of a variable. Neither form is the same as splitting text at a delimiter or searching for a word; for those tasks, other VBA functions may be a better fit.
Mid works on strings in VBA code, including text held in variables or read from worksheet cells. It does not need an Excel worksheet object when used on a string variable. That makes it handy both for small calculations in a macro and for processing rows of worksheet data.
Syntax and character positions
The function syntax is Mid(string, start[, length]). The first argument is the text to read, start is the position of the first character you want, and length is the number of characters to return. The square brackets in syntax descriptions mean that length is optional; do not type the brackets themselves in your code.
- VBA counts positions from 1, not 0. In
CODE, C is position 1 and O is position 2. - When you omit length, Mid returns all characters from the start position through the end of the string.
- A length of 1 returns one character. A length of 0 returns an empty string.
- If start is greater than the string length, Mid returns an empty string. A start value less than 1, or a negative length, causes a runtime error.
For example, Mid("CODE", 2, 2) returns OD. Knowing that indexing is one-based avoids a frequent off-by-one mistake: the first character is at position 1, even though some programming languages count from 0.
Extract text with Mid
Here is a small macro that extracts the year from a code with the format INV-2026-047. The year begins at position 5 and contains four characters. Before running any macro, save a copy of the workbook: VBA changes generally cannot be undone with Ctrl+Z.
Sub ExtractYearExample()
Dim sourceText As String
Dim yearText As String
' Read four characters beginning at position 5.
sourceText = "INV-2026-047"
yearText = Mid(sourceText, 5, 4)
Debug.Print yearText ' Prints 2026
End Sub
The output is 2026. If the same code is stored in a variable called sourceText, then Mid(sourceText, 10) returns 047: because length is omitted, the function takes the rest of the string starting at position 10. Debug.Print sends the result to the Immediate window in the Visual Basic Editor; it does not write the result into a worksheet cell.
For text that does not always have the same character positions, fixed-position extraction can return the wrong piece. In that case, find a separator first, or use a method designed to split or search text. Mid is most dependable when the input follows a known layout.
Replace characters with the Mid statement
To change part of a string variable, put the variable on the left side of an assignment using the Mid statement. This example changes the four digits in AB-1234 to 9876:
Sub ReplaceDigitsExample()
Dim labelText As String
' Replace four characters beginning at position 4.
labelText = "AB-1234"
Mid(labelText, 4, 4) = "9876"
Debug.Print labelText ' Prints AB-9876
End Sub
The statement modifies the existing variable; it does not return a new string. It also does not insert or delete characters the way an editor does. In this example, four characters are replaced by four characters, so the result remains seven characters long. This form is useful for fixed-width codes or labels where you need to update a known segment without rebuilding the whole string.
Use the function form when you need a value to assign elsewhere, such as piece = Mid(sourceText, 5, 4). Use the statement form when you intend to modify a string variable directly. Treating the two forms as interchangeable is a common source of syntax errors.
Use Mid on worksheet data
A macro can pass a cell’s value to Mid, then put the extracted text in another cell. The example below assumes a worksheet named Data, identifiers in column A beginning in row 2, and a year in characters 5 through 8. It writes the result to column B and leaves column A unchanged. Save a copy before running it because macro edits cannot be reversed with Ctrl+Z.
Sub ExtractYearsFromColumn()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
' Read IDs in column A and write extracted years in column B.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For rowNum = 2 To lastRow
ws.Cells(rowNum, "B").Value = Mid(CStr(ws.Cells(rowNum, "A").Value), 5, 4)
Next rowNum
End Sub
CStr converts the cell value to text before Mid processes it. This example assumes the cells contain ordinary text-like identifiers in the expected format. It does not validate whether each extracted four-character result is actually a year, so data with inconsistent formats should be checked before relying on the output.
On Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and run the macro with F5 while the cursor is in the procedure. On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the function keys and keyboard shortcut can vary with keyboard settings. Save a workbook containing macros as an Excel Macro-Enabled Workbook (.xlsm).
Common errors and edge cases
The most common problem is using the wrong starting position. Count every character, including spaces, hyphens, punctuation, and leading zeros. In INV-2026, the year starts at position 5 because the three letters and the hyphen occupy positions 1 through 4. If the identifier changes to INVOICE-2026, the same start value no longer points to the year.
- Start is 0 or negative: VBA cannot use it as a character position and raises a runtime error. Check the value before calling Mid if it comes from a variable.
- Start is beyond the end: the function returns an empty string rather than the requested characters.
- Length is too large: Mid returns the available characters through the end; it does not add padding.
- Length is negative: VBA raises a runtime error. A zero length, by contrast, produces an empty string.
- The source is shorter than expected: the result may be incomplete or empty. Validate the input length when a particular format is required.
For data that may be blank or malformed, add checks before extracting. For instance, you can test the string length and confirm that a delimiter appears where expected. That is safer than assuming every cell conforms to one sample value. When processing worksheet data, also consider whether cells can contain error values; converting an Excel error value directly with CStr may not behave like converting ordinary text.
VBA Mid versus Excel MID
Excel has a worksheet function named MID, and it follows the same basic one-based character idea. Its syntax requires all three arguments: text, starting position, and number of characters. VBA’s Mid function allows length to be omitted, so the two syntaxes are similar but not identical. In worksheet formulas, use the function in a cell; in a macro, use VBA Mid in code.
For example, with INV-2026-047 in cell A2, enter this in B2 to return 2026: =MID(A2,5,4). This is an Excel worksheet formula, not VBA code. It may be preferable when you want a visible result that recalculates with the cell value and do not need a macro. VBA is useful when the extraction is part of a larger automated process, or when the macro must write fixed results into a range.
Do not confuse Mid with Left and Right. Those functions return characters from the beginning or end of a string. Mid is the direct choice for a known interior position. If the position depends on a changing delimiter, a search-based approach is usually more robust than hard-coding a number.
Macro safety and compatibility
VBA macros run with the permissions of the workbook and user, so only run code from a source you trust. Keep a backup before running code that edits data; unlike a normal worksheet edit, VBA changes cannot generally be reversed with Ctrl+Z. The examples above write only to the Immediate window or, in the worksheet example, to column B, but inspect a macro before adapting it to a real workbook.
Mid is a long-standing VBA language function and is available in desktop Excel versions that support VBA, including Microsoft 365 for Windows and Mac. Excel for the web can open macro-enabled files, but it does not run VBA macros. Use desktop Excel to edit or run these procedures. If a workbook will be used in a browser, a worksheet formula or an Office Scripts solution may be more appropriate, depending on the task and the user’s environment.
Frequently Asked Questions
Does VBA Mid start counting at zero or one?
It starts at 1. In the string CAT, C is position 1, A is position 2, and T is position 3. This is true for the VBA Mid function and for Excel’s worksheet MID function.
Can I leave out the length argument?
Yes, in VBA’s Mid function. If you omit length, Mid returns all remaining characters from the specified start position to the end of the string. Excel’s worksheet MID function requires a number of characters as its third argument.
How do I replace part of a string in VBA?
Use the Mid statement with a string variable on the left side, such as Mid(labelText, 4, 4) = "9876". It replaces characters in that variable; it does not create a new string or insert characters into the middle.
Why does Mid return a blank result?
A start position greater than the string’s length returns an empty string, and a length of zero also returns an empty string. Check the actual input, count punctuation and spaces, and confirm that your start position is at least 1.
Should I use VBA Mid or the Excel MID formula?
Use the worksheet formula when you want a cell result that updates as its source changes. Use VBA Mid when a macro needs to extract or replace text as part of a procedure. Both count from position 1, but VBA Mid can omit its length argument while the worksheet function cannot.
Related
- Slicer Command in Excel: How to Add and Use It
- Excel Slicers: How to Add, Use, and Troubleshoot Them
- Circular References in Excel: Show and Find Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Set a PivotTable Filter ValueTo set a PivotTable report filter with VBA, get the PivotField and assign its item to CurrentPage: for…
- MS Access VBA: Send Email With an Attachment SafelyYes. In Microsoft Access for Windows, VBA can create an email in classic Outlook, add a file with…
- Excel VBA Hyperlink to Worksheet: A Practical GuideTo make a cell link to another worksheet with VBA, use Hyperlinks.Add and set Address to an empty…
- Excel VBA: How to Find the Last Used Column in a RowIn Excel VBA, find the last used column in a row by starting at the worksheet’s final column…
- Excel VBA: Count Visible Rows After AutoFilterTo count rows still visible after an AutoFilter, loop through the filter’s data rows and count those whose…
- Excel VBA Add AutoFilter: Code, Examples, and FixesTo add AutoFilter with Excel VBA, identify the header-and-data range and call its AutoFilter method: dataRange.AutoFilter. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.