To get one character from a string in Excel VBA, use Mid$(text, position, 1); the position starts at 1, not 0. For example, Mid$("ORANGE", 3, 1) returns A.
Use Mid$ to Get a Character by Position
Mid$ is the usual 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 last argument to 1 to extract a single character. The dollar sign in Mid$ indicates a string-returning version of the function; it avoids returning a Variant when the result is text.
VBA counts string positions from 1. In the word ORANGE, O is position 1, R is position 2, A is position 3, and E is position 6. This differs from some programming languages that use zero-based indexes. If the requested position is valid, Mid$ returns the character there:
Mid$("ORANGE", 1, 1)returnsO.Mid$("ORANGE", 4, 1)returnsN.Mid$("ORANGE", 6, 1)returnsE.
You can assign the result to a String variable, compare it with another character, or display it. A character is still represented as a string in VBA; there is no separate single-character variable type.
A Complete Macro Example
This macro reads a sample string, gets its fourth character, and displays the result in a message box. In the Visual Basic Editor, insert a standard module with Insert > Module, then place the code in that module. On Windows, open the editor with Alt+F11. On Mac, use Option+F11; depending on the keyboard, you may also need to hold Fn. Excel for the web cannot create or run VBA macros.
Option Explicit
Sub ShowCharacterAtPosition()
Dim sourceText As String
Dim position As Long
Dim result As String
' Set the text and the 1-based position to read.
sourceText = "ORANGE"
position = 4
' Return one character from the requested position.
result = Mid$(sourceText, position, 1)
MsgBox result
End Sub
The message box displays N, the fourth character of ORANGE. The macro only reads a string and displays a value, but save a copy of the workbook before running any macro that changes workbook content: VBA actions cannot be reversed with Ctrl+Z (or Command+Z on Mac).
Check the Position Before Extracting
Mid$ does not return a useful character if the requested starting position is outside the text. A start position less than 1 raises an invalid-procedure-call error. A position beyond the end of the string produces an empty string. Validate positions when they come from worksheet data, user input, or calculations rather than a fixed value.
Use Len to find the number of characters in a string. For a string with length n, valid single-character positions are 1 through n. The following example handles both an empty string and an out-of-range position by showing a message instead of attempting the extraction:
Option Explicit
Sub ShowCharacterSafely()
Dim sourceText As String
Dim position As Long
Dim result As String
sourceText = "ORANGE"
position = 8
' Reject positions that cannot identify a character.
If position < 1 Or position > Len(sourceText) Then
MsgBox "Position is outside the string."
Exit Sub
End If
result = Mid$(sourceText, position, 1)
MsgBox result
End Sub
Here, the message appears because position 8 is greater than the string length of 6. If an empty result is meaningful in your task, you can choose to return it instead of showing a message. The important point is to decide explicitly how your code should treat missing or invalid positions.
Loop Through Every Character
To inspect or process an entire string, combine Mid$ with a For loop. Start at 1 and finish at Len(sourceText). This example prints each character and its position in the Immediate window, which you can show in the Visual Basic Editor with Ctrl+G on Windows. On Mac, use the editor’s View > Immediate Window command if the shortcut differs.
Option Explicit
Sub PrintCharacters()
Dim sourceText As String
Dim position As Long
Dim currentCharacter As String
sourceText = "ORANGE"
' Visit each valid 1-based position.
For position = 1 To Len(sourceText)
currentCharacter = Mid$(sourceText, position, 1)
Debug.Print position, currentCharacter
Next position
End Sub
The output has six entries, from position 1 with O through position 6 with E. If sourceText is empty, Len(sourceText) is zero and the loop does not run. This pattern is useful for checking each character, counting digits, removing selected symbols, or building a transformed string. For larger tasks, consider whether a built-in string function or a regular expression is a better fit than processing characters one at a time.
Find a Character by Its Content
If you know what character you are looking for but not where it occurs, use InStr to find its position. It returns a 1-based position for a match and 0 when no match is found. You can then pass that position to Mid$. Specify the comparison mode when case sensitivity matters; vbBinaryCompare distinguishes uppercase from lowercase.
Option Explicit
Sub FindAndShowLetter()
Dim sourceText As String
Dim searchText As String
Dim foundAt As Long
Dim result As String
sourceText = "ORANGE"
searchText = "G"
' Search from the start using a case-sensitive comparison.
foundAt = InStr(1, sourceText, searchText, vbBinaryCompare)
If foundAt = 0 Then
MsgBox "The character was not found."
Exit Sub
End If
result = Mid$(sourceText, foundAt, 1)
MsgBox "Found " & result & " at position " & foundAt
End Sub
For these values, InStr returns 4 and the message reports that G is at position 4. Searching for lowercase g with vbBinaryCompare would return 0. If a search string occurs multiple times, InStr returns the first occurrence after the starting position; change the start position to search later in the string.
Make a Reusable CharacterAt Function
If you need the same operation in several procedures, put it in a function rather than repeating the extraction and boundary checks. This function returns an empty string when the requested position is invalid:
Option Explicit
Public Function CharacterAt(ByVal sourceText As String, _
ByVal position As Long) As String
' Return one character, or an empty string for an invalid position.
If position < 1 Or position > Len(sourceText) Then
CharacterAt = vbNullString
Else
CharacterAt = Mid$(sourceText, position, 1)
End If
End Function
After adding the function to a standard module, you can call it from another VBA procedure. You can also use a public VBA function as a worksheet user-defined function in a macro-enabled workbook. With the text ORANGE in A1, the worksheet formula =CharacterAt(A1,3) returns A. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain its VBA code, and enable macros only for workbooks you trust. The function is not available in Excel for the web.
Mid$, Left$, Right$, and Mid
Choose the string function that matches the location and amount of text you need. Mid$ can start at any position and return a specified number of characters, so it is the direct answer for a character at an arbitrary position. Left$ returns characters from the beginning, while Right$ returns characters from the end.
- Use
Mid$(sourceText, position, 1)for one character at a known position. - Use
Left$(sourceText, 1)for the first character. - Use
Right$(sourceText, 1)for the last character, after accounting for an empty string. - Use
InStrfirst when you need to locate a character by value.
VBA also has Mid without the dollar sign. As a function, it performs the same kind of extraction but returns a Variant. Do not confuse the function with the separate Mid statement, which can replace characters within an existing string variable. For simply reading a character, the Mid$ function makes the intent clear.
Common Issues: Indexing, Spaces, and Unicode
The most frequent error is treating the first position as 0. In VBA, position 1 is the first character. Another common surprise is that spaces count: in "A B", the space is position 2. Leading and trailing spaces also count, so use Trim$ only if removing them is genuinely part of the required behavior. Trimming first changes the positions of the remaining characters.
VBA strings use Unicode, but a visible symbol does not always correspond to one simple position unit. Some characters outside the basic multilingual plane are represented by a surrogate pair, and a user-perceived symbol such as a combined emoji may contain multiple code points. Consequently, extracting one VBA string position is not guaranteed to return one complete displayed symbol for every Unicode text. If your data includes emoji or complex combining characters, test with the actual content and use a Unicode-aware approach designed for grapheme clusters if complete displayed symbols are required.
Also distinguish a character’s text from its numeric code. Mid$ returns text; functions such as Asc and AscW are used when you need a character code, and their results have encoding and platform considerations. They are not substitutes for Mid$ when the goal is to display or compare the character itself.
Frequently Asked Questions
Does VBA string indexing start at 0 or 1?
For Mid$ and InStr, positions start at 1. The first character is position 1, and position 0 is not a valid starting position for this extraction.
How do I get the first character of a string in VBA?
Use Left$(sourceText, 1), or use Mid$(sourceText, 1, 1). Check for an empty string if your code needs to distinguish that case.
How do I get the last character?
For a nonempty string, use Right$(sourceText, 1). If the string could be empty, check Len(sourceText) first so your procedure can handle that case deliberately.
What happens if the position is too large?Mid$ returns an empty string when its starting position is beyond the end of the text. A position below 1 causes an error, so validate input when its range is not guaranteed.
Can I use this in an Excel cell?
Yes. Put a public function such as CharacterAt in a standard VBA module, save the file as .xlsm, and call it from a worksheet. Excel for the web does not run VBA, so use desktop Excel for that function.
Related
- Excel Split Cell Text Into Separate Cells: A Practical Guide
- Excel Split Cell Value by Delimiter: Methods and Tips
- Excel Split Cells Into Multiple Columns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA AutoFilter and Sort: Apply Both to Data SafelyYes. In Excel VBA, apply AutoFilter to a range that includes its headers, then sort that same complete…
- Excel VBA Find Method: Syntax, Examples, and TipsIn Excel VBA, use the Range.Find method to search a range and return the first matching cell; if…
- Excel VBA: Find Unique Entries in a ColumnTo find distinct entries in an Excel column with VBA, loop through the data, add each value only…
- Excel VBA Range of Object Failed? Common Causes and FixesThe Excel VBA error “Method ‘Range’ of object ‘_Worksheet’ failed” usually means VBA could not resolve the address…
- VBA MsgBox Font Size: What You Can and Cannot ChangeYou cannot set the font size of VBA’s built-in MsgBox in Excel: the function has no font-size argument…
- Excel VBA: How to Delete a Worksheet ButtonTo delete a button from an Excel worksheet, select the button itself and press Delete; for a Form…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.