In VBA, the Mid function returns part of a string, starting at a position you specify: use Mid(text, start, length), or omit length to return everything from that position to the end. Positions start at 1, so the first character is position 1—not 0.
What the VBA Mid Function Does
Mid is a built-in Visual Basic for Applications function for extracting a section of text. It is useful when a value has a predictable layout, such as a code with a prefix, a date written in a fixed format, or a longer label from which you need one section. The function returns the selected characters as a string; it does not change the original text.
For example, in the text VBA string, the letters in string begin at position 5 because the space is position 4. Mid("VBA string", 5, 6) therefore returns string. The starting position is counted from the left, and spaces and punctuation count as characters too.
VBA’s Mid is related to the MID worksheet function in Excel, but the way you call it differs. In a VBA procedure, write Mid(text, start, length) without an equals sign at the front. In a worksheet cell, use a formula beginning with =.
Syntax and Arguments
The function’s syntax is Mid(string, start, [length]). The square brackets mean that length is optional; do not type the brackets themselves. Each argument has a specific role:
stringis the text to read. It can be a string variable, a string literal, or an expression that produces text.startis the position of the first character to return. It must be a positive number; the first character is at position 1.lengthis the number of characters to return. If you leave it out, VBA returns the remaining characters fromstartthrough the end.
In code, a result can be assigned to a variable, displayed, or used as part of another expression. For example, the conceptual call Mid(value, 4, 2) asks for two characters beginning with the fourth character. If there are fewer than two characters left, the result is simply as long as the remaining text; VBA does not add padding.
How Character Positions Work
VBA counts positions from 1. This differs from programming languages and string methods that use zero-based indexes, where the first character is position 0. Applying zero-based indexing by habit is a frequent source of off-by-one mistakes in VBA.
Consider the string Excel VBA. Its positions are E=1, x=2, c=3, e=4, l=5, space=6, V=7, B=8, and A=9. So a start position of 7 selects the V. If the start position is greater than the string’s length, Mid returns an empty string. If the requested length runs past the end, VBA returns only the characters that remain.
When choosing a position, count every character, not just letters. A space, hyphen, comma, or slash takes a position. If text can vary in length, hard-coded positions may not identify the intended section reliably. In that situation, locating a delimiter with InStr and calculating a start position from its result is often safer.
Basic VBA Examples
The following macro demonstrates a fixed-length extraction and one that omits the length argument. Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. In the Visual Basic Editor, you can insert a standard module with Insert > Module and run the procedure with F5 while the cursor is inside it. On a Mac, use Option+F11 to open the Visual Basic Editor; keyboard behavior can vary with system and function-key settings.
Sub ShowMidExamples()
Dim sourceText As String
Dim selectedText As String
' Set the text that will be examined.
sourceText = "VBA string"
' Return six characters starting at position 5.
selectedText = Mid(sourceText, 5, 6)
MsgBox selectedText
' Omit length to return the rest of the text.
selectedText = Mid(sourceText, 5)
MsgBox selectedText
End Sub
Both calls in this example return string. The first explicitly asks for six characters. The second requests all remaining characters from position 5. If the source text were VBA strings, the first call would still return just string, while the call without a length would return strings.
For a shorter extraction, use a smaller length. With sourceText set to VBA string, Mid(sourceText, 5, 3) returns str. Remember that Mid returns text, so the result remains a string even if the selected characters look like digits. Convert it explicitly if you need to do arithmetic.
Extracting Text from Excel Cells
A macro can read a cell’s value, extract part of it, and place the result elsewhere. This example assumes the source text is in cell A2 of the active worksheet and writes six characters beginning at position 5 to B2. It uses CStr to convert the cell value to text; if A2 is blank, the conversion produces an empty string and the result is empty.
Save a copy of the workbook before running this macro, because changes made by VBA are not reversed by Ctrl+Z.
Sub ExtractCellText()
Dim sourceText As String
Dim extractedText As String
' Read the value from A2 as text.
sourceText = CStr(ActiveSheet.Range("A2").Value2)
' Extract six characters, starting at position 5.
extractedText = Mid(sourceText, 5, 6)
' Write the result to B2.
ActiveSheet.Range("B2").Value = extractedText
End Sub
This example is appropriate only if the text has the expected layout. If A2 contains VBA string, B2 receives string. If the source values have different prefixes or lengths, the same position may extract different content—or nothing. Check the source data before relying on a fixed position across a large range.
Excel also has a worksheet function named MID. With the text VBA string in cell A2, this worksheet formula returns str: =MID(A2,5,3). The worksheet version also counts from 1. Use the worksheet function when you want a result that recalculates with cell changes; use VBA’s Mid when writing a macro or other VBA procedure.
Using InStr When the Position Varies
A fixed starting position is fragile if a prefix can change. The VBA function InStr searches for one string inside another and returns the position where it first appears, or 0 if it does not find a match. Check for 0 before passing a search result into Mid; otherwise, a calculation based on “not found” could create an invalid start position.
This macro looks for a colon and, if one exists, returns the text after it. For a source value of Code: 4821, it displays 4821, including the space after the colon. Trimming whitespace is a separate operation and should be added if the desired result must not contain that space.
Sub GetTextAfterColon()
Dim sourceText As String
Dim colonPosition As Long
Dim resultText As String
' Set the sample text and find its delimiter.
sourceText = "Code: 4821"
colonPosition = InStr(1, sourceText, ":")
' Extract only when the colon exists.
If colonPosition > 0 Then
resultText = Mid(sourceText, colonPosition + 1)
MsgBox resultText
Else
MsgBox "No colon was found."
End If
End Sub
The + 1 starts extraction immediately after the colon. To include the colon, use colonPosition itself. This pattern is useful for labels separated by a delimiter, but it assumes the delimiter is present and sufficiently identifies the boundary you want. If the delimiter can appear multiple times, decide whether the first or last occurrence is relevant; InStrRev searches from the end.
Common Errors and Edge Cases
Most problems with Mid come from invalid arguments or assumptions about the input. A start position below 1, or a negative length, causes a run-time error. A length of 0 returns an empty string. A start position beyond the end of the string also returns an empty string, which may be valid behavior but can make a macro appear to have failed.
- Wrong start position: Count from 1 and include spaces and punctuation. Check a short example by hand before applying the code to a full data set.
- Unexpectedly empty result: Confirm that the source is long enough and that the requested starting position is not past its final character.
- Missing delimiter: If a position comes from
InStr, test whether the result is 0 before using it. - Non-text cell values: Decide how dates, numbers, blanks, or error values should be handled. Converting with
CStrcan produce text, but it does not make every source value suitable for the same extraction rule. - Changing formats: A fixed position can stop working when source data gains a prefix, changes date format, or contains extra spaces.
For robust workbook code, validate the input before extracting. For instance, check that a string is long enough for the requested section or that a required marker exists. Also be mindful of locale-dependent values: a date or number may be represented differently depending on how it was stored and converted to text, so extracting a presumed month or decimal digit by position may not be reliable.
Mid Function Versus the Mid Statement
VBA also has a feature called the Mid statement, which looks similar but is used to replace characters inside an existing string variable. The function returns a portion of a string; the statement assigns replacement text into a variable at a specified position. The distinction matters: writing part = Mid(source, 2, 2) extracts text, while using Mid(source, 2, 2) = "XY" modifies source.
The example below replaces two characters in abcdef, starting at position 3. The source becomes abXYef. The replacement has the same length as the selected section, making the intended change easy to see.
As with other macros, save a copy of the workbook before running this procedure; Ctrl+Z does not undo VBA changes.
Sub ReplaceCharactersWithMid()
Dim sourceText As String
' Prepare a string and replace two characters at position 3.
sourceText = "abcdef"
Mid(sourceText, 3, 2) = "XY"
MsgBox sourceText
End Sub
Do not confuse this statement with assigning the result of the function to a different variable. When you only need to read or extract text, use the function form. When you deliberately need to edit a string variable in place, the statement form may be appropriate.
Choosing Mid, Left, Right, or Other Tools
Choose a string function according to the location of the text you need. Mid is the general choice for a section that begins at a known position. Left returns characters from the beginning, and Right returns characters from the end. When the section’s position depends on a delimiter, combine InStr with Mid rather than assuming a fixed location.
For example, use Left for a fixed prefix and Right for a fixed suffix; use Mid for a middle section or for the remainder after a known start. If a value is separated into fields by commas or another delimiter, splitting the text into parts may be more readable than manually calculating several positions. In modern Excel worksheets, functions such as TEXTBEFORE, TEXTAFTER, and TEXTSPLIT can also suit delimiter-based tasks, but they are worksheet functions, not VBA replacements for Mid.
On the worksheet, MID is useful for a formula that should update as cells change. In a macro, VBA’s Mid is appropriate when you need to process text as part of a larger procedure. If a workbook is shared with people who may not enable macros, a worksheet formula may be easier to inspect and maintain, provided it can express the required logic.
Frequently Asked Questions
Does VBA Mid start counting at 0 or 1?
It starts at 1. The first character is position 1, and spaces and punctuation also count as characters.
What happens if I leave out the length argument?Mid(text, start) returns all characters from the starting position to the end of the text. If the start is beyond the end, the result is an empty string.
What does Mid return if the requested length is too large?
It returns the characters that remain. For example, asking for ten characters when only three remain returns those three; it does not add extra characters.
Is VBA Mid the same as Excel’s MID formula?
They have the same basic purpose and use one-based character positions. In a worksheet, use the formula form, such as =MID(A2,5,3); in VBA code, call the function as Mid(text, 5, 3).
How do I get text after a comma or colon?
Find the delimiter with InStr, check that its result is greater than 0, then call Mid starting one position after it. Decide whether to keep or remove any spaces next to the delimiter.
Can Mid change a string?
The Mid function only returns a substring. The separate Mid statement can replace characters in a string variable, so use it only when you intend to modify that variable.
Related
- Balance Sheet Accounting: How It Works
- J Curve Balance of Payments: How It Works
- Can You Play Split Fiction Online? Co-op Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Reference Is Not Valid: Causes and Fixes“The sort reference is not valid” is an Excel sorting error, not a VBA syntax rule. In a…
- Excel VBA: Check Whether a Dictionary Key ExistsUse a VBA Dictionary’s Exists method to check for a key: If dict.Exists(keyToFind) Then. It returns True when…
- Excel VBA: Get the Number of Sheets in a Workbook EasilyTo get the number of sheets in a workbook with VBA, use the workbook’s Sheets.Count property—for example, ThisWorkbook.Sheets.Count.…
- How to Use a ComboBox in an Excel VBA UserFormTo use a ComboBox in an Excel VBA UserForm, add the control in the Visual Basic Editor, fill…
- Excel Macro Enabled? What It Means and How to Use ItYes. Microsoft Excel for Microsoft 365 on Windows can run VBA macros, provided they are allowed by your…
- Excel VBA: Copy and Paste Range ValuesTo copy a range’s results without its formulas or formatting, assign the source range’s Value property to a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.