How to Split Text with TEXTSPLIT in Microsoft Excel?

Managing text data in Excel can be challenging, especially when dealing with large datasets. Fortunately, Excel’s TEXTSPLIT function makes it easy to break text into multiple cells based on a specified delimiter. This powerful function simplifies text manipulation, reducing the need for manual splitting or complex formulas.

What is the TEXTSPLIT Function?

TEXTSPLIT is a function introduced in Excel 365 and Excel 2021 that allows users to divide text based on delimiters like commas, spaces, or custom characters. Instead of using Text to Columns or formulas with LEFT, MID, RIGHT, SEARCH, and LEN, you can now split text dynamically and efficiently.

Syntax:

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_width])

• text – The text or cell reference to split.
• col_delimiter – The character that separates columns.
• row_delimiter (optional) – The character that separates rows.
• ignore_empty (optional) – TRUE or FALSE to ignore empty values.
• match_mode (optional) – Case-sensitive (0) or case-insensitive (1) matching.
• pad_width (optional) – Determines padding for uneven splits.

How to Use TEXTSPLIT in Excel

1. Splitting Text by a Space

Let’s say you have a full name in cell A2:

John Doe

To split it into first and last name in separate columns:

=TEXTSPLIT(A2, " ")

This separates “John” and “Doe” into two different cells.

2. Splitting by Comma or Other Delimiters

If a cell contains:

Apple,Banana,Cherry

Using this formula:

=TEXTSPLIT(A2, ",")

Excel will place Apple, Banana, and Cherry into separate cells in a row.

3. Splitting Text into Rows

To split text into multiple rows instead of columns, use the row_delimiter argument:

=TEXTSPLIT(A2, , CHAR(10))

If the text in A2 contains line breaks, each part will be placed into a new row.

4. Ignoring Empty Cells

If you have:

Apple,,Banana,,Cherry

And want to remove empty values:

=TEXTSPLIT(A2, ",", , TRUE)

This will only return Apple, Banana, and Cherry, skipping blank cells.

Why Use TEXTSPLIT?

• Saves time – No need for complex formulas or manual splitting.
• Dynamic updates – Works automatically when new data is added.
• More flexible – Can split text into both columns and rows.

Get the cheapest Office keys with instant delivery and hassle-free activation—upgrade your productivity today!