To extract text from a string in Excel VBA, locate the relevant characters with InStr and return them with Mid$. For example, to get the text between two known markers, find each marker’s position, then use the distance between those positions as the length to extract. Use Left$, Right$, or Split when the text’s position or separator makes those functions a better fit.
How string extraction works in VBA
VBA does not have one general-purpose function that can guess which part of a string you want. The reliable approach is to identify the rule that defines the desired text: its character positions, a delimiter, a pair of markers, or a recognizable pattern. Once you know that rule, a small set of built-in string functions can do the extraction.
For example, the string Order: AB-2048; Status: Shipped could be handled in several ways. If you know the order code always begins at character 8 and has seven characters, Mid$ can return it by position. If the code is always between a colon and a semicolon, InStr can locate those markers. If the string consists of fields separated by semicolons, Split may be more suitable.
VBA string positions start at 1, not 0. This matters when calculating where extracted text begins and how many characters to return. The dollar-sign forms, such as Mid$ and Left$, return a String; they are commonly used when the result is known to be text.
Choose the right built-in function
These functions cover most ordinary extraction tasks:
InStrsearches for a substring and returns its character position. It returns 0 when the search text is not found.Mid$returns a specified number of characters starting at a specified position.Left$returns characters from the beginning of a string;Right$returns characters from the end.Lenreturns the string’s character count, useful when the extraction runs to the end.Splitdivides a string into an array of pieces using a delimiter.Trim$removes leading and trailing ordinary spaces from a string, but it does not remove every possible whitespace character.
For a fixed-length code at a fixed location, Mid$ alone can be enough. For variable-length text between markers, combine InStr and Mid$. For clearly separated fields, use Split. These choices are not interchangeable: a delimiter-based approach can tolerate changing text lengths, while a position-based approach is simpler when the format is strictly fixed.
Extract text between two markers
Suppose cell A2 contains Order [AB-2048] confirmed, and the goal is to put the text inside the brackets in B2. This macro finds the opening bracket, searches for the closing bracket after it, and extracts the characters between them. In the VBA editor, insert a standard module with Insert > Module and paste in the code.
Option Explicit
Sub ExtractBetweenBrackets()
Dim ws As Worksheet
Dim sourceText As String
Dim openPos As Long
Dim closePos As Long
Dim extractedText As String
' Read the source text from A2 on the active worksheet.
Set ws = ActiveSheet
sourceText = CStr(ws.Range("A2").Value)
' Find the first opening bracket and its following closing bracket.
openPos = InStr(1, sourceText, "[", vbBinaryCompare)
If openPos = 0 Then
MsgBox "Opening bracket not found."
Exit Sub
End If
closePos = InStr(openPos + 1, sourceText, "]", vbBinaryCompare)
If closePos = 0 Then
MsgBox "Closing bracket not found."
Exit Sub
End If
' The result begins after [ and ends before ].
extractedText = Mid$(sourceText, openPos + 1, closePos - openPos - 1)
ws.Range("B2").Value = extractedText
End Sub
With the example input, the result in B2 is AB-2048. The length argument is closePos - openPos - 1: subtracting one excludes each marker. Searching for the closing bracket starting at openPos + 1 ensures VBA looks after the opening bracket, not before it.
Before running a macro, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z. This example handles a missing marker by showing a message and stopping; it extracts only the first bracketed section if the source contains several.
Extract from the start, end, or a fixed position
When the desired text has a fixed length and location, a position-based method is direct. For example, if every source string begins with a six-character department code, use Left$(sourceText, 6). If the final four characters are always a year, use Right$(sourceText, 4). If the text starts at character 10 and is eight characters long, use Mid$(sourceText, 10, 8).
These methods are concise but rely on consistent input. If a prefix sometimes changes from six characters to nine, a fixed-position extraction can silently return the wrong content. A safer approach is to search for a stable label or delimiter with InStr, then calculate the starting position from the marker.
To get everything after a known marker, find its position and use Mid$ from the next character through the remainder of the string. The remaining length is the total length minus the starting position plus one. Check that InStr returned a nonzero position before making that calculation; otherwise the marker was not present and the result may not represent the intended field.
Split a string using a delimiter
When a string contains a sequence of fields separated by one character or a short separator, Split turns it into an array. For instance, North|Miller|Active can be split on the vertical bar, and the returned array contains the fields at indexes 0, 1, and 2. VBA arrays produced by Split are zero-based, so the first field is at index 0 rather than 1.
This example reads A2 and writes the second field to B2, provided the source includes at least one vertical bar:
Option Explicit
Sub ExtractSecondField()
Dim ws As Worksheet
Dim sourceText As String
Dim fields() As String
' Read the delimited text and split it into fields.
Set ws = ActiveSheet
sourceText = CStr(ws.Range("A2").Value)
fields = Split(sourceText, "|")
' The second field has index 1 in a Split result.
If UBound(fields) >= 1 Then
ws.Range("B2").Value = Trim$(fields(1))
Else
ws.Range("B2").Value = ""
End If
End Sub
For North|Miller|Active, B2 receives Miller. Trim$ removes ordinary spaces around the field, which is useful for data such as North | Miller | Active. If the delimiter can appear inside a field, simple splitting is not enough; the data needs a quoting or escaping rule, or a more specific parser.
Use a regular expression for variable patterns
A regular expression can find text described by a pattern rather than a fixed marker. It is useful for formats such as a two-letter prefix, a hyphen, and four digits, or for extracting a token that can occur in different positions. Regex is more powerful than InStr, but its pattern syntax takes time to learn and can be harder for another person to maintain.
On Excel for Microsoft 365 for Windows, VBA can use the Windows VBScript.RegExp object through late binding. This example looks for a code such as AB-2048 anywhere in A2:
Option Explicit
Sub ExtractCodeWithRegex()
Dim ws As Worksheet
Dim sourceText As String
Dim regex As Object
Dim matches As Object
' Read the value and create the Windows regular-expression object.
Set ws = ActiveSheet
sourceText = CStr(ws.Range("A2").Value)
Set regex = CreateObject("VBScript.RegExp")
' Match two uppercase letters, a hyphen, and four digits.
regex.Pattern = "\b[A-Z]{2}-\d{4}\b"
regex.Global = False
regex.IgnoreCase = False
Set matches = regex.Execute(sourceText)
If matches.Count > 0 Then
ws.Range("B2").Value = matches(0).Value
Else
ws.Range("B2").Value = ""
End If
End Sub
In this pattern, [A-Z]{2} means two uppercase letters, \d{4} means four digits, and \b marks a word boundary. The code uses late binding, so it does not require selecting a reference in Tools > References. The VBScript.RegExp component is Windows-specific and should not be assumed to work in Excel for Mac. On Mac, use portable string functions or a different supported implementation, and test any required automation on the target version.
Run the code in Excel and handle many rows
On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and run a macro with F5 while the cursor is inside its procedure. On Mac, use Developer > Visual Basic to open the editor; the keyboard shortcut can depend on the keyboard and function-key settings. If the Developer tab is hidden, enable it in Excel’s ribbon settings. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop application.
For a list of records, adapt the same extraction logic to loop over a range rather than handling only A2. Set a row counter, read each cell’s value, check that the markers exist, and write the extracted value to the corresponding output row. For large ranges, it is often more efficient to read a block into a Variant array, process the values in memory, then write results back, rather than repeatedly accessing worksheet cells. A basic one-cell macro is easier to verify before scaling it up.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) to retain its code. Macros may be blocked by security settings or organizational policy; enable or run them only when you trust the workbook’s source. A macro can modify data beyond the intended cell if its range references are wrong, which is another reason to work on a copy first.
Common problems and how to avoid them
- Marker not found:
InStrreturns 0 when it cannot find a marker. Test for 0 before using the result as a position. - Wrong length calculation: When extracting between two one-character markers, subtract the two marker positions and one. If markers have multiple characters, account for the full length of the first marker.
- Unexpected capitalization: The comparison behavior can depend on the
InStrcompare argument and the module’sOption Comparesetting. PassvbTextComparefor a case-insensitive search orvbBinaryComparefor a case-sensitive one. - Extra spaces or unusual whitespace:
Trim$removes ordinary leading and trailing spaces, not every nonbreaking space, tab, or line break. Inspect the input and replace or remove specific characters when required. - Cell errors or unexpected values:
CStrconverts many values to text, but an Excel error value needs separate handling before conversion. Check the cell value withIsErrorwhen source cells may contain errors. - Duplicate markers: The example searches for the first closing marker after the first opening marker. If the text can contain nested or repeated markers, define which occurrence is intended and adjust the search logic.
A common misconception is that every extraction task needs a regular expression. For well-formed text with clear delimiters, InStr and Mid$ are usually easier to read, debug, and run across Windows and Mac. Regex is most useful when the target is defined by a genuine pattern, not simply because the string is long.
VBA versus worksheet formulas
VBA is useful when extraction is part of a repeatable process, when a macro must combine extraction with other actions, or when users need a custom routine. A worksheet formula can be more transparent for results that should update automatically as source cells change, and formulas do not require enabling macros.
In Microsoft 365, functions such as TEXTBEFORE, TEXTAFTER, and TEXTSPLIT can handle many delimiter-based tasks directly in cells; these functions require Microsoft 365 or Excel 2024. Older Excel versions may need a more involved formula or VBA. Pick the option based on who will maintain the workbook, whether results should recalculate automatically, and the versions of Excel recipients use. VBA code is not a worksheet formula and cannot be entered directly into a cell as one.
Frequently Asked Questions
How do I extract text between two characters in Excel VBA?
Use InStr to find the first character and the next occurrence of the ending character, then use Mid$ to return the text between their positions. Check that both searches succeeded before extracting.
What does InStr return if it cannot find the text?
It returns 0. Since VBA string positions begin at 1, zero is a convenient signal to test for before using a search result in Mid$.
Can I extract text after a delimiter?
Yes. Find the delimiter with InStr, then use Mid$ starting immediately after it. If the source has several fields separated by the same delimiter, Split may be simpler.
Can VBA extract text with a regular expression?
Yes, on Windows you can use the VBScript.RegExp object, as in the example above. Do not assume that object is available in Excel for Mac; use built-in string functions for more portable code.
Can I use VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to work with VBA, and account for platform-specific code such as the Windows regex object.
Related
- Excel Split Cell by Space Delimiter: Methods and Tips
- Excel Split Cell Content Into Columns: Methods That Work
- Excel Split Cell Contents by Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Try Catch: Error Handling ExplainedExcel VBA does not have a native Try...Catch statement. To handle runtime errors, use On Error GoTo to…
- Excel VBA Open File Dialog: Code and OptionsIn Excel VBA for Windows, use Application.FileDialog(msoFileDialogFilePicker) to display a file picker, let the user choose a file,…
- VBA Range Object: How to Work with CellsThe Excel VBA Range object represents one or more cells on a worksheet, letting code read or change…
- Excel VBA: Find the Last Row in a Column with DataIn Excel VBA, the usual way to find the last row with data in column A is ws.Cells(ws.Rows.Count,…
- Excel VBA UserForm Activate vs InitializeIn Excel VBA, use UserForm_Initialize to set up a form when it is loaded, and use UserForm_Activate for…
- Excel VBA Hex String: Convert Numbers to HexadecimalIn Excel VBA, use the Hex function to turn a number into a hexadecimal string: Hex(255) returns FF.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.