In VBA, use Split(text, delimiter) to break a string wherever a chosen delimiter appears; the result is an array of text pieces. For example, Split("red,green,blue", ",") returns three values. You can loop through the array, set a limit on the number of pieces, or write the pieces to worksheet cells.
The steps below are for Excel for Microsoft 365 on Windows. Split is a VBA function, so you run it from a macro or call it in other VBA code; it is not an Excel worksheet formula. Save a copy before running any macro that writes to the workbook, because VBA changes cannot be reversed with Ctrl+Z.
What You’ll Need
- Excel desktop with VBA support. Excel for the web cannot run VBA macros.
- A text value and a clear delimiter, such as a comma, semicolon, tab, or pipe character.
- A workbook saved as an Excel Macro-Enabled Workbook (
.xlsm) if you want to keep the macro in that file. - A safe, blank area for any output your macro will write. Make a backup copy first.
Step-by-Step
- Identify the text and separator. Decide what string you want to divide and the exact characters that separate its parts. For
"Maya;Leo;Nina", the delimiter is a semicolon. Split looks for the delimiter you provide; it does not automatically detect commas, spaces, or other separators. - Open the Visual Basic Editor. In Excel for Windows, press
Alt+F11. You can also select Developer > Visual Basic. If the Developer tab is missing, enable it from File > Options > Customize Ribbon by checking Developer. On a Mac, useOption+F11(some keyboards requireFnas well), or open the Visual Basic Editor from Excel’s Tools or Developer menu. The editor’s menus may differ slightly by Excel version. - Insert a standard module. In the Visual Basic Editor, choose Insert > Module. A standard module is a suitable place for a small macro. If you are adding Split to an existing procedure, you can instead put the relevant statements inside that procedure.
- Declare a variable for the result. Split returns a one-dimensional array held in a Variant, so a Variant variable is convenient when you want to use the result directly. Declare a separate Long variable for a loop counter. Declaring variables makes it easier to spot misspellings and manage the array’s values.
- Call Split with the string and delimiter. The basic syntax is
Split(expression, [delimiter], [limit], [compare]). Only the expression is required, but specifying the delimiter makes your intent clear. For example,Split(sourceText, ";")splits at each semicolon. The returned array normally starts at index zero, not index one. - Try a small, safe macro. This example reads a comma-separated value from cell A1 on the active sheet, trims spaces from each piece, and writes the results down column B. Put a sample such as
red, green, bluein A1, then choose a blank column B before running it.
Sub SplitCellIntoColumn()
Dim ws As Worksheet
Dim sourceText As String
Dim pieces As Variant
Dim i As Long
Set ws = ActiveSheet
' Stop if A1 contains an Excel error or no text.
If IsError(ws.Range("A1").Value2) Then
MsgBox "Cell A1 contains an error value."
Exit Sub
End If
sourceText = CStr(ws.Range("A1").Value2)
If Len(sourceText) = 0 Then
MsgBox "Enter comma-separated text in A1 first."
Exit Sub
End If
' Split at commas, then write each item to column B.
pieces = Split(sourceText, ",")
For i = LBound(pieces) To UBound(pieces)
ws.Cells(i + 1, 2).Value = Trim$(CStr(pieces(i)))
Next i
End Sub
- Save and run the macro carefully. Save a copy of the workbook, then save the working file as
.xlsmif it contains VBA. In the editor, click inside the procedure and pressF5, or return to Excel and use Developer > Macros, selectSplitCellIntoColumn, and choose Run. On Mac, the macro dialog is also available from the Developer or Tools menu; the shortcut and menu arrangement can vary. - Check the output and adapt the delimiter. With
red, green, bluein A1, the macro writesred,green, andblueto B1:B3. For semicolon-separated text, change the second argument to";". If your source is in another cell, change"A1"; if you need output elsewhere, change the column number inws.Cells(i + 1, 2).
The function’s optional arguments are useful when the simple call is not enough. limit sets the maximum number of array elements. For example, Split("red,green,blue", ",", 2) returns red and green,blue; the unprocessed remainder stays in the last element. The optional compare argument controls how VBA matches the delimiter. You can pass vbBinaryCompare for a binary comparison or vbTextCompare for a text comparison. Being explicit is helpful when matching a delimiter whose letters may vary in case.
Split separates strings, not worksheet cells or files by itself. If your data is in a cell, VBA first reads that cell’s value, then Split processes the resulting text. Similarly, the function does not write its array back to a sheet; your code must loop over the elements or use another appropriate output method.
Common Mistakes
- Using the wrong delimiter. A comma and a comma followed by a space are not the same delimiter. With
"red, green"and a comma delimiter, the second item begins with a space. UseTrim$on each piece if surrounding spaces should be removed. - Assuming the array starts at one. Split normally returns a zero-based array. A loop beginning at 1 skips the first item. Use
LBound(pieces)andUBound(pieces)rather than hard-coding bounds. - Expecting multiple separators to work automatically. A call with a comma delimiter will not also split at semicolons or pipes. If the source uses several separators, normalize them first or process the text in stages.
- Forgetting empty items. In
"red,,blue", the two adjacent commas create an empty element betweenredandblue. Decide whether that empty value is meaningful before removing or skipping it. - Overwriting existing data. The example writes to B1 and downward. If those cells contain information, the macro replaces it. Pick an empty destination and save a backup; Ctrl+Z does not undo a macro.
Tips
- Use a delimiter that cannot be confused with ordinary content when you control the source format. A pipe or a distinctive multi-character separator can be safer than a comma if values may themselves contain commas.
- Split accepts a multi-character delimiter, such as
"||", as one separator. It does not treat a list of characters as alternatives: passing",;"searches for that exact two-character sequence. - Use
Trim$when leading and trailing spaces are accidental. Do not trim automatically if spaces are meaningful parts of the original value. - When processing rows of imported data, inspect a few source strings first. Quoted CSV fields can contain commas as data, and a basic Split call does not understand CSV quoting rules. Use a proper CSV import or parser for that format.
- Keep the original string in a separate variable if later code needs it. Split does not change the source string; it creates an array from it.
Troubleshooting
The result has only one item. Check that the delimiter occurs exactly in the source. A one-item result is expected when there is no match; the item is then the original string. Check for a different separator, an unexpected space, or a delimiter that was copied incorrectly.
The first value is missing or the loop gives an error. Start the loop at LBound(pieces), not at 1. Also check whether the source is an empty string. Split returns an empty array for a zero-length expression, so calling LBound or UBound on that result can fail. Test the string’s length before splitting, as the sample macro does.
There are extra spaces in the output. Split preserves spaces that are part of the original string. Wrap each element in Trim$(CStr(pieces(i))) when writing or processing it, or use a delimiter that includes the space if that matches your input consistently.
The macro will not run. Confirm the file is open in Excel desktop, the code is in a standard module, and macros are permitted by your organization’s security settings. Excel for the web does not run VBA. Do not enable macros in an untrusted workbook just to test the procedure.
The output is in the wrong place or old results remain. The sample uses the active sheet and writes from B1 down. Activate the intended sheet before running, or set ws to a specific worksheet. If a previous run created more items than the current one, clear only the intended output range after checking it; do not clear an entire column without confirming its contents.
Frequently Asked Questions
Does VBA Split return an array?
Yes. It returns a one-dimensional array of substrings inside a Variant. You can access each element by its index and use LBound and UBound to find the available bounds.
How do I split text into separate worksheet cells?
Read the source cell into a string, call Split, then loop through the array and assign each value to a cell. The example writes down column B. To write across a row instead, use a cell reference with a fixed row and a changing column index.
Can Split use a space as the delimiter?
Yes. Pass a space in quotation marks, as in Split(sourceText, " "). This splits at each individual space, so repeated spaces can produce empty elements. If you want to clean up outer spaces, apply Trim$ as well.
How can I split on more than one delimiter?
Split accepts one delimiter per call, not a set of alternatives. You can replace other separators with one common character before splitting, or run additional processing for each separator. Choose a replacement character that does not already appear in the data.
Is Split case-sensitive?
The optional compare argument controls delimiter matching. Specify vbBinaryCompare for binary matching or vbTextCompare for text-style matching when case differences matter. If you omit the argument, VBA uses its default comparison behavior.
Can I undo a Split macro with Ctrl+Z?
No. Excel clears its normal undo history when VBA changes cells, so Ctrl+Z will not reverse a macro’s output. Save a copy first, and have the macro write to a checked, empty range.
Related
- How to Add Transitions in PowerPoint: Steps and Tips
- T Balance in Accounting: Meaning, Examples, and Uses
- Is Split Fiction Cross-Platform? Crossplay Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Label Not Defined: Fix the Compile ErrorExcel VBA’s Label not defined compile error means a procedure refers to a jump target that VBA cannot…
- Refresh Worksheet VBA: Methods, Examples, and FixesTo recalculate formulas on one worksheet with VBA, call that worksheet’s Calculate method. To refresh imported data, use…
- VBA Code to Open an Excel File from SharePointIn Excel for Microsoft 365 on Windows, use VBA’s Workbooks.Open method with the file’s direct SharePoint URL, provided…
- VBA Range.Insert: Insert Cells, Rows, and Columns in ExcelIn VBA, use the Range.Insert method to add cells, rows, or columns and shift existing content out of…
- How to Delete Blank Rows in Excel with a VBA MacroTo delete blank rows in Excel with a macro, select the data range, run a VBA procedure that…
- VBA Delete Row If Cell Contains TextTo delete rows when a cell contains particular text, use VBA’s InStr function in a loop that checks…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.