In VB.NET, split a string with its String.Split method, such as Dim parts As String() = text.Split(","c). It returns an array of substrings separated by the specified delimiter; in VBA, the similarly named Split function is used instead, with slightly different syntax and behavior.
Which Visual Basic Split Should You Use?
“Visual Basic” can refer to current VB.NET or to VBA, the Visual Basic for Applications language used in programs such as Excel and Access. The basic idea is the same in both: provide text and a delimiter, then work with the pieces. The API and some options are different, so examples written for one environment should not be copied blindly into the other.
For a VB.NET application, the usual choice is the Split method on a string. It is available on the .NET String type and returns a String() array. In VBA, use the built-in Split function, which returns a zero-based, one-dimensional array held in a Variant. This article covers both, while using VB.NET for the main examples.
Basic VB.NET Syntax
Call Split on the string you want to divide and supply a separator. When the separator is one character, the Visual Basic character-literal suffix c makes it a Char rather than a one-character string:
Dim text As String = "red,green,blue"
Dim parts As String() = text.Split(","c)
' parts contains "red", "green", and "blue".
The method returns an array, so you can get an item by its zero-based index, loop over the pieces, or check how many were returned with parts.Length. For example, parts(0) is "red", and parts(2) is "blue". An index outside the array bounds raises an exception, so check the array length before requesting an item when the input might vary.
A simple loop processes every element without assuming how many pieces the input contains:
For Each part As String In parts
Console.WriteLine(part)
Next
For code that needs a stable result, declare the variable as String() as shown. This makes it clear that the result is an array of strings, not one combined string.
How Delimiters Work
A delimiter marks where one piece ends and the next begins. With a character separator, Split looks for that character throughout the input. It does not treat a list of separator characters as a multi-character token: if you supply comma and semicolon as separators, each comma or semicolon is independently a split point.
Dim text As String = "red;green,blue"
Dim separators As Char() = {";"c, ","c}
Dim parts As String() = text.Split(separators, StringSplitOptions.None)
Here, the array contains three values: "red", "green", and "blue". Use a string separator array when the delimiter is a sequence of characters that must stay together, such as a double pipe:
Dim text As String = "north||south||east"
Dim parts As String() = text.Split(New String() {"||"}, StringSplitOptions.None)
This separates at each complete || token. Choosing the right overload matters: a Char() separator array means “split on any one of these characters,” while a String() separator array can contain multi-character delimiters.
Empty Fields, Consecutive Delimiters, and Trimming
By default, StringSplitOptions.None preserves empty entries. That is often useful when positions have meaning, but it can surprise you if the input contains repeated or trailing separators:
Dim parts As String() = "red,,blue,".Split(","c)
The result has four elements: "red", an empty string, "blue", and another empty string. The empty second element represents the missing value between the two commas; the final empty element represents the delimiter at the end. Removing these values can be convenient, but it also discards that positional information.
Use StringSplitOptions.RemoveEmptyEntries when empty results should be omitted. It removes zero-length entries, but it does not by itself remove strings that contain only spaces. In .NET 5 and later, combine it with StringSplitOptions.TrimEntries to trim whitespace from each result and omit entries that become empty:
Dim parts As String() = " red, ,blue ".Split(
New Char() {","c},
StringSplitOptions.TrimEntries Or StringSplitOptions.RemoveEmptyEntries)
This produces "red" and "blue". If the project targets an older .NET version without TrimEntries, trim each result yourself, then decide whether to keep or discard blank values. Do not remove empty entries automatically when empty fields are meaningful, as they can indicate missing data that should be validated.
Splitting on Whitespace or Several Characters
To split on any of several characters, pass a character array. For example, a text field that may use commas, semicolons, or tabs can be divided at any of those characters. If no separator is provided, or the separator array is empty, .NET treats whitespace characters as separators. Be explicit when a particular delimiter is required; an omitted or empty separator is not the same as asking for an empty-string delimiter.
Whitespace splitting can be useful for quick tokenization, but it may not suit data where repeated spaces must be preserved. Likewise, TrimEntries changes the values by removing surrounding whitespace. Decide whether whitespace is formatting to discard or part of the data before applying it.
Using Split in VBA
In VBA, call the Split function with the expression first and the delimiter second. A basic example is:
Sub ShowSplit()
Dim parts As Variant
Dim i As Long
' Divide the text at each comma.
parts = Split("red,green,blue", ",")
For i = LBound(parts) To UBound(parts)
Debug.Print parts(i)
Next i
End Sub
The function returns a zero-based array by default, and using LBound and UBound makes the loop bounds explicit. VBA also accepts optional limit and compare arguments. The limit controls the maximum number of substrings; the compare argument can select binary or text comparison behavior. If omitted, comparison follows the function’s documented default, which can be affected by the module’s Option Compare setting.
The examples in this section are VBA, not VB.NET code. Before running a VBA macro, save a copy of the workbook or other file: macro actions cannot be undone with Ctrl+Z. That precaution is useful even when a short example only prints values, because real macros may also change cells, files, or application state.
Common Problems and Misconceptions
- Expecting a single result:
Splitreturns an array. Assign the result to an array variable and use an index or loop to access its elements. - Confusing a character array with a phrase: A
Char()array splits on any listed character. Use aString()array for a multi-character delimiter such as"||". - Ignoring empty entries: Consecutive or final delimiters can produce empty strings. Inspect the output or choose an appropriate split option rather than assuming every field has content.
- Assuming Split understands a file format: It does not parse quotation rules, escaped delimiters, or nested structures. A comma inside a quoted CSV field is still a comma to a basic string split.
- Mixing VBA and VB.NET syntax: The names look similar, but VBA’s function and VB.NET’s method have different signatures and return-type conventions.
When a field is required, validate the resulting array and its values before using them. For example, a date or number should be parsed with an appropriate conversion method and checked for failure, rather than assumed valid because it appeared between delimiters.
When to Choose Another Parsing Method
Split is a good fit for simple, predictable text such as a list of tags separated by semicolons or a two-part value separated by a known marker. It is not a general-purpose parser. CSV is a common trap: a record such as North,"Harbor, East",12 contains a comma inside a quoted field, so splitting at every comma breaks the quoted name into two pieces. Use a CSV-aware parser when quoted fields, escaped quotes, or multiline values are possible.
For more complex patterns, regular expressions can identify separators that depend on context, though they require careful pattern design. For structured formats such as JSON or XML, use a parser for that format instead of trying to split the text manually. The more rules a format has, the more likely a basic delimiter split is to produce incorrect fields.
Frequently Asked Questions
Does Visual Basic Split return an array?
Yes. VB.NET’s String.Split returns a String() array. VBA’s Split returns a zero-based, one-dimensional array, commonly stored in a Variant.
How do I split a string at a space in VB.NET?
Use Dim parts As String() = text.Split(" "c) to split at each ordinary space character. To split on whitespace more generally, use a whitespace separator approach or call Split without a separator, and check that the behavior matches your input requirements.
How can I split on more than one delimiter?
In VB.NET, pass a Char() containing the characters when any one of them is a separator. Use a String() when delimiters are multi-character tokens. In VBA, the function accepts one delimiter argument, so handling several alternative delimiters may require preprocessing or a different parsing approach.
How do I remove blank results?
In VB.NET, pass StringSplitOptions.RemoveEmptyEntries. If fields may contain only whitespace and you want those removed too, use TrimEntries together with that option on a .NET version that supports it.
Can String.Split parse CSV correctly?
Not reliably. A basic split does not account for quoted fields or escaped delimiters. Use a CSV parser for CSV input so commas inside quoted fields are interpreted as data rather than separators.
Related
- Python Logging F-Strings: When to Use Them and Why
- Onshape Split Tool: How to Split Parts and Faces
- Oracle SQL String Split: Methods and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Python Goal Seek Like Excel? Methods and ExamplesYes. Python can do the same basic job as Excel Goal Seek: adjust one input until a formula…
- How to Make a Booklet in Word Back to Back PrintingTo make a back-to-back booklet in Word, set the document’s page layout to Book fold, arrange your content…
- Excel Solver Not Finding a Solution? Troubleshoot ItIf Excel Solver is not finding a solution, first check that the objective cell depends on the changing…
- Excel Shortcut for Filling a Cell YellowIn Excel for Microsoft 365 on Windows, press Alt, then H, then H to open the Fill Color…
- Excel Redo Shortcut: How to Reapply an ActionIn Excel for Microsoft 365 on Windows, press Ctrl+Y to redo the last action you undid. On a…
- How to Change Every Other Row Color in Excel AutomaticallyTo change every other row color in Excel, select your data and use either a table style for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.