Visual Basic string functions help you measure, search, extract, compare, change, and split text. In VBA, functions such as Len, Mid$, InStr, and Replace are common; in VB.NET, you can use those compatibility functions or the methods of the .NET String type, such as Substring, IndexOf, and Trim.
Visual Basic has two common meanings
“Visual Basic” can refer to different programming environments. VBA (Visual Basic for Applications) is embedded in programs such as Excel and Word and is commonly used for macros. VB.NET is a .NET language used to build applications. Many familiar text functions appear in both, but their conventions and available alternatives are not identical.
VBA examples often use functions such as Left, Right, and Mid. VB.NET supports many of these through the Microsoft.VisualBasic namespace, but also offers string instance methods and .NET APIs. A VBA code sample is not automatically interchangeable with a VB.NET sample: project references, data types, indexing, and error behavior can differ. When looking up a function, check whether its documentation is for VBA, VB.NET, or another Visual Basic version.
Core functions for measuring and extracting text
Len returns the length of a string. Left and Right take a specified number of characters from the beginning or end. Mid extracts characters beginning at a specified position. In the traditional Visual Basic functions, positions start at 1, so the first character is position 1.
Len(text)counts the characters represented by the string.Left(text, count)returns up tocountcharacters from the left.Right(text, count)returns up tocountcharacters from the right.Mid(text, start, [length])extracts a section from the 1-based start position. If length is omitted, it returns the remaining text.
For example, Mid("Visual Basic", 8, 5) returns Basic: the B is the eighth character. In VBA, the dollar-suffixed forms, such as Left$ and Mid$, return a String rather than a Variant. They are useful when you want the return type to be explicit; the unsuffixed forms remain widely used.
VB.NET’s Substring method is a common alternative, but its start index is zero-based. For the same word, "Visual Basic".Substring(7, 5) returns Basic. Mixing up a 1-based function position and a 0-based method index is a frequent source of off-by-one errors.
Finding text and checking where it appears
InStr searches for one string inside another and returns the position where the match begins. Its traditional result is 1-based, and it returns 0 when there is no match. You can specify a starting position and, where appropriate, a comparison mode. For example, InStr(1, "Visual Basic", "Basic", vbTextCompare) returns 8 and requests a text comparison that is not case-sensitive under the applicable locale rules.
InStrRev searches from the end of a string toward the beginning. It is useful for finding the last slash in a path or the last dot in a filename, but a dot is not necessarily a reliable way to identify a file extension: names can contain multiple dots, and a path may contain directories with dots.
In VB.NET, IndexOf is a standard search method. It uses zero-based indexing and returns -1 when the value is not found. For example, a search for Basic in Visual Basic returns 7. If case-insensitive matching is required, choose an overload that accepts a StringComparison value rather than changing both strings’ case just to compare them.
Use the result deliberately: test for 0 with InStr, or for -1 with IndexOf. Treating either result as a position without checking whether the search succeeded can produce invalid extraction logic.
Replacing, trimming, and changing case
Replace returns text in which occurrences of a search string are substituted with another string. In VBA, its optional arguments can control the start position, number of replacements, and comparison mode. VB.NET also has a String.Replace method, though its overloads and comparison behavior differ from VBA’s function.
Trim removes leading and trailing spaces; LTrim removes leading spaces, and RTrim removes trailing spaces. The .NET String.Trim method removes whitespace at both ends by default, which can include more whitespace characters than an ordinary space. Neither approach removes spaces inside the text: trimming " Visual Basic " produces "Visual Basic", not "VisualBasic".
UCase and LCase convert text to uppercase or lowercase in VBA and in the Visual Basic compatibility functions. In .NET code, ToUpperInvariant and ToLowerInvariant are useful when a consistent, culture-independent form is needed for technical identifiers. For language-sensitive display text, culture-aware rules may be more appropriate. Changing case is not always equivalent to case-insensitive comparison, so use a comparison option designed for that purpose when possible.
Comparing strings and joining text
StrComp compares two strings and returns a value indicating whether the first is less than, equal to, or greater than the second. The optional comparison argument can request binary or text comparison. In VBA, Option Compare can also affect the default comparison behavior for certain operations in a module. For predictable code, specify the comparison mode where the function allows it, and do not assume that a comparison is case-insensitive unless you have selected that behavior.
Use the ampersand operator (&) to concatenate text in Visual Basic. For example, firstName & " " & lastName joins two names with a space. Although + can concatenate strings in some Visual Basic contexts, it can also perform numeric addition or introduce conversion ambiguity. The ampersand communicates that the operation is text concatenation.
Split divides a string at a delimiter and returns an array of pieces; Join combines an array of strings using a delimiter. These are useful for simple, consistently delimited data, such as a list of tags separated by commas. They do not parse every data format correctly: splitting CSV text on commas, for example, fails when a quoted field itself contains a comma. Use a CSV parser for CSV data.
Formatting and creating repeated text
Format converts a value into text according to a format expression. In VBA, it is frequently used for dates, numbers, and currency-like display. Formatting changes presentation, not the underlying value. If text will later be used for calculations, keep the value numeric or date-typed as long as possible and format it only when producing output.
Space returns a string containing a requested number of spaces, while String creates a string by repeating a specified character. For example, the VBA expression String$(5, "-") returns -----. These functions can be handy for fixed-width output or simple visual separators, though modern user interfaces usually handle layout with controls rather than padding text manually.
Practical VBA and VB.NET examples
This VBA example trims an input, checks for a word, and extracts it. It uses InStr’s 1-based result and checks for a failed search before calling Mid$.
Dim phrase As String
Dim position As Long
phrase = Trim$(" Visual Basic ")
position = InStr(1, phrase, "Basic", vbTextCompare)
If position > 0 Then
Debug.Print Mid$(phrase, position, 5)
End If
The output is Basic. In a VB.NET program, the corresponding .NET methods use a zero-based index. This version searches without case sensitivity and checks for the .NET not-found result before taking a substring.
Dim phrase As String = "Visual Basic"
Dim position As Integer = phrase.IndexOf(
"Basic", StringComparison.OrdinalIgnoreCase)
If position >= 0 Then
Console.WriteLine(phrase.Substring(position, 5))
End If
The output is also Basic, but the index is 7 rather than 8. The examples illustrate why it helps to identify which function family a code sample uses before translating it.
Common mistakes and edge cases
- Confusing empty text with missing data: An empty string has length zero, but it is not the same concept as a missing value. VBA can also represent
Null; VB.NET can useNothingfor a string reference. Handle these cases according to the type and environment. - Assuming every character has a simple one-to-one length: Unicode text can include characters represented by more than one UTF-16 code unit, and some visible symbols combine multiple code points. Length and indexing APIs may count underlying units rather than what a person perceives as a single displayed character.
- Ignoring case and locale: Text comparison rules can vary by function, settings, and culture. For machine-readable identifiers, choose a deliberate, consistent comparison strategy; for human language, consider the relevant linguistic rules.
- Assuming replacement edits the original: Strings are immutable in .NET. Methods such as
Replacereturn a new string, so assign the result if you need to keep it. - Using repeated concatenation for large output: For a small number of pieces,
&is clear. In a .NET loop that builds a large amount of text,StringBuildercan be a more suitable tool.
Frequently Asked Questions
What is the Visual Basic function for string length?Len(text) returns the length of a string in VBA and in the Visual Basic compatibility functions. VB.NET strings also have a Length property, as in text.Length.
How do I get part of a string in Visual Basic?
Use Left, Right, or Mid for the traditional functions. In VB.NET, Substring is a common method. Remember that the traditional functions use 1-based positions, while Substring uses a zero-based index.
How do I check whether a string contains another string?
Use InStr and test whether its result is greater than zero in VBA. In VB.NET, use IndexOf and test whether the result is at least zero; IndexOf returns -1 when it finds no match.
What is the difference between Trim and Replace?Trim removes whitespace at the start and end of text. Replace substitutes matching text wherever the requested search and replacement rules apply; it does not automatically remove surrounding spaces.
Are VBA and VB.NET string functions the same?
They overlap, but they are not interchangeable in every detail. Traditional functions such as Mid use 1-based positions, while common .NET string methods use zero-based indexes, and comparison options and types can differ.
Related
- JavaScript split() Method: Syntax, Examples, and Tips
- JavaScript .split(): How It Works and When to Use It
- C++ String Erase: How std::string::erase Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Absolute References in Excel Table FormulasYes—you can use absolute cell references in Excel table formulas by adding dollar signs to the cell address,…
- Combine Cells in Excel That Have Words: Formulas and StepsTo combine words from two Excel cells, enter a formula such as =A2&" "&B2 in an empty cell;…
- XLOOKUP Practice Examples for Everyday Excel TasksFor an exact-match lookup, put the value to find in one cell, then use XLOOKUP to search a…
- XLOOKUP Advanced Examples for Real-World Excel TasksXLOOKUP advanced examples include returning several columns at once, matching on multiple conditions, finding the last occurrence, and…
- Excel COUNTIF Over a Number: Greater Than CriteriaTo count numbers greater than a value in Excel, use =COUNTIF(A2:A100,">50"), replacing the range and 50 with your…
- XLOOKUP Between Two Sheets in Excel?Yes. To look up a value on one Excel sheet and return a related value from another, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.