A split function test checks whether a text-splitting formula or tool correctly divides a string into the expected parts. In Excel, the primary split functions are TEXTSPLIT (Microsoft 365 and Excel 2024) and the legacy Text to Columns wizard; testing them means feeding sample strings, verifying delimiters, and handling edge cases like empty values or multiple spaces.
What the Split Function Does
Excel’s TEXTSPLIT function breaks a text string into separate cells based on a delimiter (like a comma, space, or semicolon). For example, =TEXTSPLIT("apple,banana,cherry",",") returns three cells: apple, banana, cherry. The function spills results horizontally by default, but you can set the row_delimiter argument to split vertically. This is a modern replacement for older methods like LEFT/RIGHT/MID combined with FIND, which required complex nesting.
How to Run a Split Function Test
Testing a split function involves creating a controlled set of inputs and comparing the output to expected values. Here is a step-by-step approach in Excel for Microsoft 365:
- Place sample strings in column A (e.g., A1: “red,green,blue”, A2: “one;two;three”, A3: “no delimiter”).
- In B1, enter
=TEXTSPLIT(A1,",")and press Enter. The results spill to the right. - Copy the formula down for other rows, adjusting the delimiter as needed.
- Check for errors: #VALUE! appears if the delimiter is not found? Actually,
TEXTSPLITreturns the original text if the delimiter is missing, so no error—but that might be unexpected. Test with a delimiter that exists and one that doesn’t. - Test edge cases: empty string (A4: “”), consecutive delimiters (A5: “a,,b”), and leading/trailing spaces (A6: ” x , y “).
For a more automated test, use IF and COUNT to compare results. For instance, if you expect two parts from “a,b”, you can check =IF(COUNTA(TEXTSPLIT(A1,","))=2,"Pass","Fail").
Common Problems and Misconceptions
A frequent misconception is that TEXTSPLIT handles multiple spaces as a single delimiter. It does not—it splits on every occurrence of the delimiter. So TEXTSPLIT("a b"," ") returns three parts: “a”, “”, “b”. To treat multiple spaces as one, you need to use the TRIM function first or use a regular expression (which Excel does not support natively). Another issue: TEXTSPLIT is not available in Excel 2019 or earlier. If you use it there, you get a #NAME? error. Also, if the split result exceeds the available cells, you get a #SPILL! error—this happens when adjacent cells are not empty.
How to Choose the Right Split Method
Your choice depends on your Excel version and the complexity of the task. Use TEXTSPLIT if you have Microsoft 365 or Excel 2024 and need a dynamic, formula-based split that updates when the source changes. Use Text to Columns (Data tab > Data Tools > Text to Columns) for a one-time split that overwrites data—it is static and works in all versions. For simple splits with a fixed number of parts, the LEFT, MID, and RIGHT functions with FIND are still viable, but they are clunky. For example, to get the first word: =LEFT(A1,FIND(" ",A1)-1). That works only if there is a space; otherwise it returns a #VALUE! error.
Comparing TEXTSPLIT with Text to Columns
Here is a quick comparison:
- TEXTSPLIT: Dynamic, formula-based, spills results, updates automatically, requires Microsoft 365 or Excel 2024.
- Text to Columns: Static, overwrites original data, works in every Excel version, supports multiple delimiters and fixed-width splitting.
- Power Query: Best for large datasets or repeated transformations; you can split columns by delimiter in the Query Editor (Data tab > Get & Transform Data > From Table/Range).
For a one-off cleanup, Text to Columns is simpler. For a live dashboard, TEXTSPLIT is superior.
Testing with Real-World Examples
Consider a column of full names: “John Smith”, “Mary Jane Watson”, “Oscar”. To split into first and last names, you might use =TEXTSPLIT(A1," "). For “John Smith”, that gives two cells. For “Mary Jane Watson”, it gives three—so the test fails if you expect exactly two columns. A better approach for names is to use LEFT and RIGHT with FIND for the first and last, but middle names complicate it. Always test with a representative sample that includes edge cases like missing delimiters or extra spaces.
Frequently Asked Questions
Why does my TEXTSPLIT return #SPILL! error? The spill range is blocked by non-empty cells. Clear the cells to the right (or below if using row_delimiter) and try again. Also, if you have a table, the spilled array may not fit within the table boundaries.
Can I split by multiple different delimiters at once? Yes, TEXTSPLIT accepts an array of delimiters. For example, =TEXTSPLIT(A1,{",",";"}) splits on both commas and semicolons. Text to Columns also allows this by checking “Treat consecutive delimiters as one” and specifying multiple delimiters.
Does TEXTSPLIT work in Google Sheets? No, Google Sheets has a different function called SPLIT. In Google Sheets, =SPLIT(A1,",") works similarly but uses a different syntax and is not case-sensitive for delimiters. Excel’s TEXTSPLIT is not available in Sheets.
How do I split a string into rows instead of columns? Use the second argument of TEXTSPLIT: =TEXTSPLIT(A1,","," ") splits on commas into columns and spaces into rows. Or set the row_delimiter only: =TEXTSPLIT(A1,,",") to split vertically.
Is there a way to ignore empty values when splitting? TEXTSPLIT has an ignore_empty argument. Use =TEXTSPLIT(A1,",",,TRUE) to skip empty parts. Text to Columns always removes empty values by default, but you can change that with the “Treat consecutive delimiters as one” option.
Safety and Accuracy Notes
When testing split functions, always work on a copy of your data—especially when using Text to Columns, which overwrites the original column. For formulas, remember that TEXTSPLIT is volatile in the sense that it recalculates when the source changes, so ensure your workbook is set to automatic calculation. Also, be careful with delimiters that are special characters like backslash or pipe; they do not need escaping in Excel, but they do in some programming contexts. Finally, if you are splitting data that contains formulas, the results are static text, not formulas.
Related
- Arduino String Data Type: How It Works and When to Use It
- Snowflake SPLIT: Turn a String Into an Array
- C++ String Data Type: How std::string Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF for Text in Excel: Examples and WildcardsTo count cells containing a particular text value in Excel, use COUNTIF: with the values to check in…
- Filter an Excel Column Based on a List of ValuesTo filter a column in Excel for Microsoft 365 on Windows, select your data, choose Data > Filter,…
- Microsoft Excel Calendar Formula: Build a Monthly ViewIn Microsoft Excel for Microsoft 365, enter a year in B1 and a month number in C1, then…
- COUNTIF for X in a Cell: Exact or Partial MatchTo count cells whose entire value is x, use =COUNTIF(A2:A8,"x"); to count cells that contain x anywhere, use…
- How to Hide Filter Arrows in Excel and Keep Table FiltersTo hide filter arrows in Excel without removing a table’s AutoFilter setup, click inside the table, open Table…
- XLOOKUP Function Examples: Exact, Approximate, and MoreXLOOKUP searches one range for a value and returns the corresponding value from another range. For example, with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.