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])
stringis the text to read. It can be a string variable, a string literal, or a value that VBA can convert to text.startis the character position where extraction begins. Positions are one-based: the first character is position 1.lengthis 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:
Middoes not locate words. Find a changing position withInStrfirst, 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:
Midworks with string character positions, not byte offsets. For binary data or byte-oriented encodings, use byte functions such asMidBonly 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
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns and Rows: How to Split Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Open an IQY File Without PromptsYes. In Excel for Microsoft 365 on Windows, VBA can open an .iqy file with Workbooks.Open, while temporarily…
- On Error in Excel VBA: How to Handle Runtime ErrorsOn Error is the VBA statement that controls what a procedure does when a runtime error occurs in…
- VBA Dictionary Declaration: Setup, Methods, and ExamplesTo declare a VBA Dictionary in Excel for Microsoft 365 on Windows, use Dim dict As Object followed…
- Excel VBA Unhide Sheet: Methods and Common FixesTo unhide a worksheet with VBA, set its Visible property to xlSheetVisible, such as Worksheets("Summary").Visible = xlSheetVisible. For…
- VBA Access Arrays: Declare, Loop, and Resize ThemIn Microsoft Access VBA, an array is a variable that holds multiple values of the same declared type,…
- Excel VBA Prompt to Open a File: Code and OptionsTo prompt someone to choose a workbook and then open it in Excel VBA, call Application.GetOpenFilename and pass…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.