A Guide to Using the New TEXTSPLIT() Function in Microsoft Excel

Microsoft Excel has always been a powerful tool for managing, analyzing, and visualizing data. Over the years, Excel has introduced new functions that simplify complex tasks and improve productivity. One of the most notable recent additions is the TEXTSPLIT() function. This function allows users to split text strings into multiple cells quickly and dynamically, eliminating the need for older, more cumbersome methods such as “Text to Columns” or manual parsing using LEFT, RIGHT, and MID functions.

In this guide, we will explore the TEXTSPLIT() function, its syntax, use cases, practical examples, tips, and common mistakes to avoid. By the end of this article, you will have a thorough understanding of how to use TEXTSPLIT() to make your Excel workflows more efficient and dynamic.

Introduction to TEXTSPLIT()

The TEXTSPLIT() function is part of Excel’s suite of dynamic array functions, introduced in Excel 365 and Excel 2021. Unlike traditional formulas, dynamic array functions allow results to “spill” automatically into adjacent cells without requiring manual copying or dragging. TEXTSPLIT() is designed to split a text string into multiple cells based on specified delimiters, which can include spaces, commas, semicolons, or even custom characters.

Before TEXTSPLIT(), splitting text required either using the Text to Columns wizard, which is manual and static, or using complex combinations of formulas like LEFT, RIGHT, MID, SEARCH, and FIND. With TEXTSPLIT(), the process becomes intuitive, flexible, and dynamic.

2. Syntax of TEXTSPLIT()

• text: This is the string or cell reference that you want to split. It can be a single word, a sentence, or even a full paragraph.

• col_delimiter: The character or set of characters that Excel will use to split the text into columns. For example, a comma or a space.

• row_delimiter: Optional. The character(s) to split the text into rows instead of columns.

• ignore_empty: Optional. Set this to TRUE to ignore empty values when two delimiters are consecutive, or FALSE to include them.

• match_mode: Optional. Use 0 for an exact match or 1 for case-insensitive splitting.

Splitting Text by a Single Delimiter

The simplest and most common use of TEXTSPLIT() is splitting a text string by a single delimiter. For instance, imagine you have a cell containing the text “John,Paul,George,Ringo”. To split this string into separate columns, you would use the formula:

This formula divides the text at every comma, creating four separate cells, one for each name. The results spill automatically into adjacent columns without the need to drag the formula.

Similarly, if you want to split a string by spaces instead of commas, such as “John Paul George Ringo”, you simply replace the delimiter:

This splits the string at every space, creating individual cells for each word. This is particularly useful when dealing with full names, addresses, or phrases.

Splitting Text into Rows

While splitting text into columns is common, sometimes you may want the results to appear in rows instead. This can be done by specifying the row_delimiter while leaving the column delimiter blank. For example, if you have the text “John;Paul;George;Ringo” and you want each name to appear in a separate row, you can use:

Notice the double commas. The first comma represents the column delimiter, which is left blank, and the semicolon acts as the row delimiter. Excel then fills each row below the original cell with the names. This method is particularly useful for lists or data that need to be displayed vertically.

Handling Multiple Delimiters

One of the most powerful features of TEXTSPLIT() is the ability to split text using multiple delimiters. For example, if your data contains both commas and semicolons, such as “John,Paul;George,Ringo”, you can define multiple delimiters using curly braces:

This formula instructs Excel to split the text at every comma or semicolon, creating a clean set of individual values. This is ideal for cleaning messy data imports where multiple separators are used, such as CSV exports or user-submitted data.

Ignoring Empty Values

When dealing with data that may contain consecutive delimiters, empty cells can appear in the output. TEXTSPLIT() allows you to ignore these empty values by using the ignore_empty parameter.

For instance, consider the string “Apple,,Banana,,Cherry”. Using the formula:

will return only “Apple”, “Banana”, and “Cherry”, ignoring the empty entries. If ignore_empty is set to FALSE or omitted, Excel will create blank cells wherever two commas appear consecutively.

Case-Insensitive Splitting

Sometimes, the case of letters matters, especially when splitting text for comparisons or filtering. The match_mode parameter allows you to control whether Excel splits text in a case-sensitive or case-insensitive manner.

For example, if you have the string “Apple,apple,APPLE” and you want to treat all variations the same for splitting purposes, you can use:

Setting match_mode to 1 ensures case-insensitive splitting, which can prevent duplication or mismatch issues.

Practical Examples

TEXTSPLIT() is versatile and can be applied to a wide range of practical scenarios.

1. Splitting Full Names: If you have a list of full names, such as “John Doe” and “Jane Smith”, you can split first and last names by space. This is useful for creating separate columns for first and last names in a database.

2. Extracting Data from CSV Strings: When importing data from CSV files, TEXTSPLIT() can replace manual parsing. For example, a CSV string like “Product,Price,Stock” can be split into three separate columns, and any subsequent rows of data will automatically align with these headers.

3. Cleaning Messy Text Data: For text data containing inconsistent delimiters, TEXTSPLIT() allows you to standardize the output efficiently. A string like “Red,Blue;Green,Yellow” can be split into four distinct values using multiple delimiters.

4. Dynamic Arrays for Dashboards: TEXTSPLIT() works well with other dynamic array functions like SORT(), UNIQUE(), and FILTER(). This allows you to create dynamic dashboards where text input is split, filtered, and displayed automatically in real-time.

Common Mistakes to Avoid

Even though TEXTSPLIT() is straightforward, several mistakes can hinder its functionality:

1. Incorrect use of row and column delimiters: Remember to leave the column delimiter blank if splitting into rows only.

2. Not handling empty values: Decide whether to ignore empty entries, especially when consecutive delimiters exist.

3. Using the wrong delimiter: Ensure that the delimiter exactly matches the character in your text, including spaces.

4. Old Excel versions: TEXTSPLIT() is only available in Excel 365 and Excel 2021. Attempting to use it in older versions will result in errors.

Advanced Tips and Tricks

1. Combining with Other Functions: You can combine TEXTSPLIT() with other functions like UPPER(), LOWER(), TRIM(), or CONCAT() to clean and manipulate text further. For instance, =UPPER(TEXTSPLIT(A1, ",")) will split the text and convert all results to uppercase.

2. Sorting Split Data: After splitting a list, you can sort the results dynamically using the SORT() function. For example, =SORT(TEXTSPLIT(A1, ",")) will produce a sorted list automatically.

3. Creating Dynamic Dashboards: TEXTSPLIT() can extract labels or categories from single-cell inputs and feed them directly into charts, tables, or reports. This is especially useful when building dashboards that require dynamic updates based on user input.

4. Nested Splitting: For complex text strings, you can nest multiple TEXTSPLIT() functions. For example, first splitting a string into rows, then splitting each row into columns, allowing multi-dimensional parsing of data.

Conclusion

The TEXTSPLIT() function is a major improvement for Excel users who frequently work with text data. It simplifies text splitting, reduces manual effort, and works dynamically with other formulas to create intelligent and automated spreadsheets.

Key takeaways:

• TEXTSPLIT() allows splitting text into rows, columns, or both.

• Multiple delimiters are supported, making it flexible for messy data.

• The function supports dynamic arrays, so results update automatically when the source text changes.

• Proper use of parameters like ignore_empty and match_mode ensures clean, accurate results.

• TEXTSPLIT() is compatible only with Excel 365 and Excel 2021, so older versions will not support it.

Adopting TEXTSPLIT() in your workflow will save time, reduce errors, and make text management in Excel far more efficient. Whether you are a beginner or an advanced Excel user, mastering this function is highly beneficial for everyday tasks, data analysis, and dynamic spreadsheet creation.