How to use the TEXTSPLIT function in Excel to easily split text

Splitting text accurately in Excel is a skill that is becoming increasingly important, especially with the new features that have been incorporated in the most recent versions of Excel. Microsoft 365. The TEXTSPLIT function was created in response to the need to process text strings in a simple and flexible way., allowing you to separate words, phrases or even complex lists in just a few steps and with a single formula.

In this article, you will find an extremely complete guide on how to use TEXTSPLIT, from the basics to more advanced uses. Here you'll discover how to split names and phrases, work with multiple delimiters, configure the function to be case-sensitive, and even how to customize the results to get the most out of your spreadsheets.

What is the TEXTSPLIT function in Excel?

TEXTSPLIT is a function that allows you to split text into multiple columns or rows. directly from a cell, using custom delimiters. It's available to Excel users with Microsoft 365 in the Insider Channel, making it a very modern and efficient option compared to traditional methods.

Unlike older procedures, such as the Convert Text to Columns Wizard, TEXTSPLIT offers much more versatility and control to adapt text division to any data structure you need to analyze or manipulate.

Syntax of the TEXTSPLIT function

The general format for using the function is as follows:

=TEXTSPLIT(text, col_delimiter, , , , )

Now, let's break down the arguments to find out exactly what you can do with each one:

• text: This is the data or text string you want to split. It can be in a single cell or referenced from another cell (for example, A2).
• delimiter_col: Defines the character, character, or string where Excel will divide the text into columns. Required.
• row_delimiter: (Optional) If you specify, you can also have the text split into rows based on a different delimiter.
• ignore_empty: (Optional) Specifies whether empty cells generated by splitting should be removed (TRUE) or kept (FALSE, default).
• match_mode: (Optional) Determines whether the delimiter search is case-sensitive. 0 is case-sensitive, 1 is case-insensitive.
• fill_with: (Optional) If the resulting array has spaces, you can specify which character or value you want to display in the empty spaces. If you omit this option, Excel displays a #N/A error.

Main advantages over traditional methods

Traditionally, the column-to-column splitting wizard was used to split text in Excel, which still exists and is practical for specific tasks. However, TEXTSPLIT allows you to do it all with a single formula., which can be easily replicated and adapted when changing data or automating processes.

In addition, the function offers the possibility of dividing both in columns and rows simultaneously, work with multiple delimiters in one step, and control behavior in empty cells or missing values, which represents a significant improvement for advanced users.

How to split text into columns using TEXTSPLIT

The most common operation is to separate the contents of a cell into multiple columns, for example, to divide first and last names, phrases, or lists separated by a specific delimiter.

Suppose you have in cell A2 the text “Dakota Jones Sanchez”If you want to split that text into multiple columns using a space as a separator, you can use:

=TEXTSPLIT(A2, » «)

In this case, Excel will display in adjacent cells each part of the first and last name., that is: Dakota | Jones | Sanchez.

If your case is a comma-separated list, as in “Apple, Orange, Banana”, it will be enough to adjust the delimiter:

=TEXTSPLIT(A2, «,»)

This way you will get the content distributed in different columns, without the need to use the wizard or perform additional steps.

Split text into rows

The function is not limited to columns, but can also be used to split the content into rows. To do this, the delimiter_col parameter is left empty (indicated by two commas) and the row delimiter is specified.

For example:

=TEXTSPLIT(A2,, «,»)

The text will be divided into different rows within the same column, useful for transforming horizontal lists into vertical ones or vice versa, adapting to the way you want to present the information.

Split text into rows and columns at once

In situations where the text is organized as an embedded table (for example, values separated by “:” and rows by commas), You can separate both columns and rows at the same time:

=TEXTSPLIT(A2, «:», «,»)

With this, you get a matrix organized in two dimensions, where each element is perfectly structured, ideal for transforming complex data into easy-to-work tables.

How to use multiple delimiters

A very useful case is when the text string contains different separator characters. For example, you have a text with both hyphens and commas. TEXTSPLIT allows you to use multiple delimiters simultaneously specifying an array constant with all of them:

=TEXTSPLIT(A2, {"-",","})

Here, Excel will split text at both hyphens and commas., without you having to take extra steps or combine functions.

Case-sensitive options

By default, TEXTSPLIT is case-sensitive for delimiters. If you need that does not distinguish between upper and lower case (e.g. split by “EXAMPLE” regardless of whether it appears as “example”, “Example”, etc.), use match_mode set to 1:

=TEXTSPLIT(A2, "Example",,,1)

This ensures more flexible text processing, especially useful in mixed listings or listings originating from different sources.

Managing empty cells and missing values

One of the most powerful advantages of TEXTSPLIT is Advanced handling of empty cells or missing values when splitting text. By default, any empty cells created (for example, if there are two delimiters close together and nothing between them) remain in the result. If you prefer to have them removed immediately, you can specify TRUE in the ignore_empty section:

=TEXTSPLIT(A2, «,», ,TRUE)

Esto automatically removes empty spaces, avoiding errors or confusion with incomplete data.

On the other hand, when dividing into 2D arrays (rows and columns at a time), you may encounter gaps or missing data. In that case, it is possible to define a fill text or symbol so that, instead of the #N/A error, the character or word of your choice appears:

=TEXTSPLIT(A2, «:», «,», , , «-«)

Thus, The gaps will be covered by the “-” character, allowing for a much more orderly and professional presentation of reports.

Practical examples of using TEXTSPLIT

The best way to understand the function is to see real-life examples. Imagine you have a list of employees, each with their full name in a cell:

Employee
Dakota Jones Sanchez

If you want to separate first and last names into different columns, use:

=TEXTSPLIT(A2, » «)

To break a simple sentence into individual words (for example, “To be or not to be”):

=TEXTSPLIT(A3, » «)

If you have lists of products separated by periods (.), you can easily adapt the delimiter:

=TEXTSPLIT(A2, «.»)

And if the text contains both commas and periods, you can use:

=TEXTSPLIT(A2, {«,», «.»})

Alternatives: Text to Columns Wizard

Although TEXTSPLIT is a significant improvement, in versions prior to Microsoft 365 you will have to resort to the classic wizard:

1. Select the cell or column with the text to split.
2. Go to Data > Text to Columns.
3. Choose the option Delimited and tap Next.
4. Select the appropriate delimiters (comma, space, period, etc.). You can see a preview of the result.
5. Choose Destination where you want to place the results and press Finalize.

This method still works but is not as dynamic as TEXTSPLIT, as it does not automatically update if data changes or allow for the same advanced customization or automation options.

How to work with custom delimiters or variables

A great strength of TEXTSPLIT is that accepts any character or word as a delimiterAlthough the usual examples use quotation marks, periods, commas, or spaces, you can enter any word, symbol, or even multiple options at once.

For example, to divide by equal signs (=) and semicolons (;):

=TEXTSPLIT(A2, {«=», «;»})

This offers a much greater level of customization, which is necessary when working with data from different systems or export formats.

Advanced Configuration: Combining Options

You can combine several of the function's options to get exactly the result you need:

• Case-insensitive splitting, skipping empty cells:
  =TEXTSPLIT(A2, «x», , , 1, «-«)
• Splitting into rows and columns at the same time and customizing spaces:
  =TEXTSPLIT(A2, «:», «,», , , «-«)
• Splitting with multiple delimiters and preserving gaps:
  =TEXTSPLIT(A2, {«-«, «,»})

Other examples of formulas for splitting text

Although TEXTSPLIT is the most powerful option, there are classic formulas that can still be useful in very specific contexts, especially in older versions of Excel. Some examples:

• Left to extract name and surname:
  =LEFT(A2, FIND(» «,A2,FIND(» «,A2,1)+1))
• Extract second surname:
  =EXTRACT(A2,FIND(» «,A2,FIND(» «,A2,1)+1)+1,FIND(» «,A2,FIND(» «,A2,FIND(» «,A2,1)+1)+1)-(FIND(» «,A2,FIND(» «,A2,1)+1)+1))
• Right to extract the last surname or word:
  =RIGHT(A2,LENGTH(A2)-FIND(» «,A2,FIND(» «,A2,FIND(» «,A2,1)+1)) )

Although these formulas manage to split texts in specific contexts, they do not offer the flexibility or ease of use of TEXTSPLIT., and they can become very complicated if the text is variable or contains more words than usual.