To get one character from a VBA string by position, use Mid$(text, position, 1). For example, Mid$("Excel VBA", 7, 1) returns V; VBA string positions start at 1, not 0.
Use Mid$ to Get a Character
Mid$ is usually the clearest choice when you know the character’s position. Its arguments are the source string, the starting position, and the number of characters to return. Set the final argument to 1 to request a single character. The dollar sign indicates that the function returns a String rather than a Variant.
Sub GetOneCharacter()
Dim sourceText As String
Dim position As Long
Dim result As String
sourceText = "Excel VBA"
position = 7
' Check the position before extracting.
If position >= 1 And position <= Len(sourceText) Then
result = Mid$(sourceText, position, 1)
Debug.Print result ' Prints V.
Else
Debug.Print "Position is outside the string."
End If
End Sub
The sample checks the position before calling Mid$. This matters because a position of zero or less is invalid and can cause a run-time error. A starting position beyond the end of the string returns an empty string, but explicitly checking the range makes the intent clearer and helps prevent errors when the position comes from a cell or user input.
In Excel for Microsoft 365 on Windows, put this code in a standard VBA module: open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure. On a Mac, use Option+F11 to open the editor; depending on the keyboard, you may also need to hold Fn. The string function works the same on both platforms. Excel for the web cannot run or edit VBA macros.
How VBA String Positions Work
VBA numbers positions from 1. In the string "Excel VBA", E is position 1, x is position 2, the space is position 6, and V is position 7. This is different from languages and APIs that use zero-based indexes, where the first item is at position 0. If you copy an index from such a source, adjust it before using it with VBA’s string functions.
The third argument to Mid$ is a length, not an ending position. For instance, a start of 3 and a length of 2 returns the characters at positions 3 and 4. If you omit the length, Mid$ returns the text from the starting position through the end of the string.
Sub ShowMidExamples()
Dim sourceText As String
sourceText = "Excel VBA"
Debug.Print Mid$(sourceText, 1, 5) ' Excel
Debug.Print Mid$(sourceText, 7) ' VBA
Debug.Print Mid$(sourceText, 6, 1) ' One space
End Sub
Use Len(sourceText) to get the string’s length when you need to validate an index or loop over its characters. For a normal text string, the final valid position is Len(sourceText). An empty string has a length of zero, so it contains no character to retrieve.
Loop Through Every Character
When you need to inspect or process a whole string, combine Mid$ with a For loop. The loop starts at 1 and ends at the string length. Each pass extracts the character at the current position. This pattern is useful for examining input, counting punctuation, printing characters for debugging, or applying a rule one character at a time.
Sub PrintCharacters()
Dim sourceText As String
Dim position As Long
Dim currentCharacter As String
sourceText = "VBA"
' Visit each valid position from left to right.
For position = 1 To Len(sourceText)
currentCharacter = Mid$(sourceText, position, 1)
Debug.Print position, currentCharacter
Next position
End Sub
The Immediate window displays the position and character for each iteration. In the Visual Basic Editor, show that window with Ctrl+G on Windows. On a Mac, the menu command View > Immediate Window is a dependable way to open it; keyboard shortcuts can vary with the keyboard and system settings.
A loop over an empty string performs no iterations because its length is zero. If you use the extracted character to change worksheet values, remember that running a macro can change workbook content and cannot be undone with Ctrl+Z. Save a copy of the workbook before running a macro that makes changes.
When Left$ or Right$ Is Simpler
If the character is always at one end of the text, Left$ or Right$ can express the goal more directly. Left$(text, 1) returns the first character, and Right$(text, 1) returns the last character. Both functions take a count, so a count of one returns one character.
Sub GetEndCharacters()
Dim sourceText As String
Dim firstCharacter As String
Dim lastCharacter As String
sourceText = "Report"
If Len(sourceText) > 0 Then
firstCharacter = Left$(sourceText, 1)
lastCharacter = Right$(sourceText, 1)
Debug.Print firstCharacter ' R
Debug.Print lastCharacter ' t
End If
End Sub
Use Mid$ for a character at a known interior position or when the position changes in a loop. Use Left$ for a prefix and Right$ for a suffix. These functions are not interchangeable in every situation: Left$ and Right$ count from an edge, while Mid$ lets you specify where to begin.
Find a Character When Its Position Is Unknown
If you know the character or text to look for but not its position, use InStr rather than checking every position yourself. It returns the position of the first match, or zero if there is no match. For example, InStr(1, sourceText, "-", vbBinaryCompare) searches from position 1 for a hyphen using a binary comparison.
Sub FindHyphen()
Dim sourceText As String
Dim foundAt As Long
Dim foundCharacter As String
sourceText = "AB-123"
foundAt = InStr(1, sourceText, "-", vbBinaryCompare)
If foundAt > 0 Then
foundCharacter = Mid$(sourceText, foundAt, 1)
Debug.Print foundAt, foundCharacter ' 3 and -
Else
Debug.Print "No hyphen found."
End If
End Sub
Searching for a one-character string will tell you where that character first appears. If you want to find every occurrence, repeat the search from the position after the previous match, or loop through the string and compare each character. For case-insensitive searches, use vbTextCompare as the final argument to InStr. Supplying the comparison mode explicitly makes the code’s behavior easier to understand than relying on a module-level Option Compare setting.
Handle Empty Text and Invalid Positions
Code that reads a position from a worksheet, a form, or another procedure should validate it before calling Mid$. A valid position is a whole number from 1 through Len(text). If the source is empty, no position is valid. If the position is less than 1, VBA can raise an error; if it is greater than the string length, the result is empty.
For example, if cell A1 contains the text and cell B1 contains the requested position, convert and validate the position before extracting. Cell values can be blank, errors, or unexpected text, so a production macro may also need to check the value’s type before assigning it to a Long. Avoid assuming that every worksheet cell contains the kind of data its heading suggests.
Also distinguish a blank cell from a string containing a space. A space is a character and has a length of 1; a zero-length string has a length of 0. If a result appears blank, it may be an actual space. To make that visible while debugging, print the value between markers, such as brackets, or inspect its length with Len.
Common Misconceptions and Character Limits
A frequent mistake is writing Mid$(text, 0, 1) because another language uses zero-based indexing. In VBA, the first position is 1, so the first character is Mid$(text, 1, 1). Another common mistake is treating the third argument as an ending position. It is the number of characters to return; use a length of 1 when you want one character.
There is also a subtle distinction between a visible symbol and a character position. VBA strings use Unicode text, but some symbols outside the Basic Multilingual Plane, including many emoji, are represented by a pair of UTF-16 code units. VBA’s traditional string functions may treat that pair as two positions, so extracting one position may not produce the complete visible symbol. For ordinary letters, digits, and common punctuation, the simple one-position pattern is generally what readers expect. For text containing complex emoji or combining marks, do not assume that each visible glyph equals one VBA string position.
Finally, Mid and Mid$ are related but not identical in return type: Mid returns a Variant, while Mid$ returns a String. In code where you want text, Mid$ is a clear, conventional choice. Use Mid if you specifically need a Variant result or are following existing code that uses it.
Frequently Asked Questions
How do I get the first character of a string in VBA?
Use Left$(text, 1) or Mid$(text, 1, 1). Check that the string is not empty if the code depends on receiving a character.
How do I get the last character of a string in VBA?
Use Right$(text, 1) when the string is not empty. You can also use Mid$(text, Len(text), 1), but Right$ states the intention more clearly.
Does VBA start counting string positions at zero or one?
VBA’s Mid, Left, and Right string positions and counts use 1-based positions. The first character is at position 1.
What does Mid return if the position is beyond the end?
If the starting position is greater than the string length, Mid$ returns a zero-length string. A start position less than 1 is invalid, so validate positions that come from external input.
Can I use VBA string functions in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel app to run VBA; the standard Mid$, Left$, and Right$ functions work in desktop Excel for Windows and Mac.
Related
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox String: Build and Display MessagesA VBA MsgBox string is the text you pass to the MsgBox function to display a message in…
- VBA Excel: Copy a Range of CellsTo copy a range of cells in VBA, use the range’s Copy method and specify the top-left destination…
- Easy Macro Recorder in Excel: How to Record and Use OneYes. Microsoft Excel for Microsoft 365 includes a built-in macro recorder: choose Developer > Record Macro, perform the…
- VBA Could Not Find File? Causes, Checks, and FixesIf VBA says it could not find a file, the path or filename supplied to the macro does…
- Insert an Image in a VBA UserFormTo insert an image in an Excel VBA UserForm, open the form in the Visual Basic Editor, add…
- How to Run Macros in Google Sheets: A Step-by-Step GuideTo run a macro in Google Sheets, open the spreadsheet in a desktop web browser, choose Extensions >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.