The Ultimate Guide to Excel’s TEXTSPLIT Function

Microsoft Excel is constantly evolving, introducing new functions that make data manipulation easier and more efficient. One of the most powerful additions in recent versions is the TEXTSPLIT function. If you’ve ever struggled with breaking text into separate columns or extracting specific parts of a string, this function will be a game-changer for you.

In this guide, we’ll dive deep into TEXTSPLIT, explaining what it does, how it works, and how you can use it to simplify your Excel workflow.

What is the TEXTSPLIT Function?

The TEXTSPLIT function in Excel allows you to split text based on a delimiter (such as a comma, space, or hyphen) and distribute the results across multiple columns or rows. It is a more dynamic and flexible alternative to the Text to Columns feature and formulas like LEFT, RIGHT, MID, and SEARCH.

TEXTSPLIT Syntax

=TEXTSPLIT(text, column_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Arguments Explained:

• text – The text string you want to split.
• column_delimiter – The character(s) that define where the text should split into columns.
• [row_delimiter] – (Optional) The character(s) that define where the text should split into rows.
• [ignore_empty] – (Optional) A TRUE or FALSE value to ignore empty cells in the result. Default is FALSE.
• [match_mode] – (Optional) Controls case sensitivity (0 = case-sensitive, 1 = case-insensitive).
• [pad_with] – (Optional) Specifies a value to fill missing data when the split text results in uneven columns or rows.

How to Use TEXTSPLIT in Excel

1. Splitting Text into Columns

If you have a list of full names in one column and want to separate first and last names into different columns, you can use TEXTSPLIT like this:

Example:

Formula:

=TEXTSPLIT(A2, " ")

This formula splits the name at the space (” “), placing the first name in column B and the last name in column C.

2. Splitting Text into Rows

If you have a single cell containing multiple values separated by commas and want to distribute them into different rows, you can do this easily with TEXTSPLIT.

Example:

Data in A1:

Apple, Banana, Orange, Mango

Formula:

=TEXTSPLIT(A1, , ", ")

Result (in separate rows):

Here, we skipped the column delimiter and specified the row delimiter as ", " so that each fruit appears in a separate row.

3. Handling Multiple Delimiters

What if your text contains both commas and spaces as delimiters? You can specify multiple delimiters by using an array inside {}.

Example:

Data in A1:

Apple, Banana Orange; Mango

Formula:

=TEXTSPLIT(A1, {", ", "; "})

Result:

Here, we used both ", " and "; " as delimiters, ensuring that the function correctly splits the text into separate values.

4. Ignoring Empty Cells

If your text contains consecutive delimiters, the default behavior of TEXTSPLIT is to include empty cells. However, you can ignore empty results by setting the ignore_empty argument to TRUE.

Example:

Data in A1:

John;;Doe

Formula:

=TEXTSPLIT(A1, ";", , TRUE)

Result:

By setting ignore_empty to TRUE, the empty cell between the two semicolons (;;) is skipped.

5. Splitting Text with Case Sensitivity

If you need to split text while ignoring case sensitivity, you can use the match_mode argument.

Example:

Data in A1:

Excel;EXCEL;excel

Formula:

=TEXTSPLIT(A1, ";", , , 1)

This formula ensures that the function treats “Excel”, “EXCEL”, and “excel” the same way while splitting.

Why Use TEXTSPLIT Instead of Other Methods?

✅ More Dynamic than Text to Columns – Updates automatically when new data is added.
✅ Eliminates the Need for Complex Formulas – No need for multiple LEFT, MID, RIGHT, or SEARCH functions.
✅ Works with Multiple Delimiters – Unlike traditional methods, TEXTSPLIT can handle multiple separators.
✅ Splits Data into Rows and Columns – Unlike SPLIT in Google Sheets, which only works with columns, TEXTSPLIT supports both rows and columns.

Get the cheapest Office keys with instant delivery and full activation—100% genuine and secure!