Working with strings in Microsoft Excel is common, whether you’re parsing, concatenating or returning a character from the middle of another string. You might receive data with names all in the same cell. To parse them into autonomous entities using functions requires complex expressions — until now. Fortunately, Excel now offers easy-to-use features, such as Power Query, Text To Columns and even Flash Fill. Now, you can add TEXTSPLIT() to that list.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

In this tutorial, we’ll review Excel’s new TEXTSPLIT() function. You’ll learn that this function does a bit more than simply parse characters.

TEXTSPLIT() is available to Microsoft 365 subscribers, which includes Excel for the web. If you’re working with an earlier version, use Text To Columns on the Data tab instead, which doesn’t support all that TEXTSPLIT() can do, but it’s a good start. If you’re sure you have Microsoft 365, and you don’t have this function, please talk to your administrator.

You can download the Microsoft Excel demo file for this tutorial.

How to use TEXTSPLIT() in Excel

Microsoft has been busy this last year adding new string functions to Microsoft Excel: TEXTAFTER(), TEXTBEFORE(), TEXTJOIN() and TEXTSPLIT(). They all have string management in common. TEXTSPLIT() parses, or “splits,” strings using column and row delimiters, much in the same way Microsoft’s Text To Columns feature works with columns, but in a function.

There are a few differences between TEXTSPLIT() and Text To Columns:

• Text To Columns overwrites the original data by returning the first parsed value in the same cell as the original value.
• Text To Columns supports only one delimiter and it’s a column delimiter. It doesn’t work with rows at all.

Now let’s learn about this new text function, which uses the following syntax:

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

An explanation for these arguments follows:

• text: This required argument is the text you want to split.
• col_delimiter: This required argument is the text that marks the point to begin the split across columns.
• row_delimiter: This text marks the point to begin the split down rows.
• ignore_empty: If TRUE, the default, this argument ignores empty cells. If FALSE, it creates an empty cell if there are two consecutive delimiters.
• match_mode: The default value, 0, matches case when matching the delimiter. To ignore case, use 1.
• pad_with: A value to pad the result. The default is #N/A.

Remarkably, this function can handle more than one delimiter when expressed as an array. For instance, the function TEXTSPLIT(A1,{“,” , “.”}) will split the string in A1 using a comma or a period. The space characters between the two delimiters aren’t necessary but make the arguments easier to read.

Now let’s move on to a few simple examples.

How to use TEXTSPLIT() on an irregular data set in Excel

Parsing strings is difficult with the older functions, especially if the data is inconsistent. Thanks to TEXTSPLIT() this task is much simpler. Let’s consider the simple data set shown in Figure A. The function in C3

=TEXTSPLIT(B3,"/")

and copied to the remaining cells easily parses most of the values as expected — at the / character. This function is a dynamic array function. That means it spills across as many columns as necessary, but there’s only one function and that’s in C3.

Figure A

Notice that the function doesn’t care how many characters are before or after the delimiter. However, the value in B8 has no delimiter and the value in C9 contains a space instead of the / character. In both cases, the problem is inconsistent data that needs attention, but the function doesn’t return that information. Instead, it returns the original value.

The first consideration is the delimiters. It’s possible that the values might use both characters. If that’s the case, the function

=TEXTSPLIT(B3,{"/"," "})

in E3 and copied to the remaining cells handles both delimiters as shown in Figure B. That leaves us with only the value in B8 to resolve. To the best of my knowledge, TEXTSPLIT() has no argument that handles values with no delimiter at all. The empty cells will be a good clue though.

Figure B

How to parse dates using TEXTSPLIT() in Excel

Another common parsing task is dates. We generally enter dates as an entire date but also need the components. Instead of using the more complex date functions, use TEXTSPLIT() as shown in Figure C. The local system is using United States regional settings, so if you’re not in the U.S., you’ll need to accommodate the format difference when specifying the format string in the TEXT() function.

Figure C

The function in C3 and copied to the remaining cells

=TEXTSPLIT(TEXT(B3,"m/d/yyyy"), "/")

uses the TEXT() function to split the dates by month, day, and year. A few other possibilities follow:

• "mmm/dd/yyyy" returns Oct 13 2022
• "mmm/dd/yyyy" returns Oct 2022
• "yyyy" returns 2022
• "ddd" returns Thur

That’s just a few to consider. The only thing that limits the possibilities is your knowledge of these date formats.

This use is simple, but not intuitive because you must add the TEXT() function. Let’s look at another use that might not occur to you.

How to ignore characters when using TEXTSPLIT() in Excel

Another parsing problem occurs when you receive inconsistent data. For example, some of the names shown in Figure D have titles and some don’t. There’s no argument for this problem, but with a little creativity, you can get the job done: Specify the titles as part of the delimiter, but even this becomes a bit more complex when you must ignore several titles. You must enter the titles as an array.

Figure D

The function in C3 and copied to the remaining cells

=TEXTSPLIT(B3,{" ","Ms.","Mrs."},,TRUE)

isn’t perfect, but it gets you close. The second record’s title, Dr., and the middle name initial are a special problem that you’ll need to accommodate in some way. The third record includes a middle name. The clue is the unexpected values in columns E and F. You might consider running a Replace task to remove the titles before parsing. You will run into situations where TEXTSPLIT() can’t get the job done alone but the results will usually point to problems in the original value.

How to parse rows using TEXTSPLIT() in Excel

You know the basics, and you’ve even seen two unusual situations where TEXTSPLIT() is helpful. Now let’s use this function with rows. It works the same way, but instead of parsing across columns, it parses down rows.

Figure E shows a simple array in B3 and the function

=TEXTSPLIT(B3,,",")

in C3, which spills the values in B3 down as many columns as needed. Notice that I’m using the row_delimiter this time.

Figure E

There’s one more nuance that you’ll appreciate. In Figure F, notice that I’ve changed the delimiter between the numbers 3 and 4 to a semi-colon and updated the function in F3 to accommodate it in the col_dilimiter argument:

=TEXTSPLIT(E3,",", ";")

Figure F

That’s right, you can use both the row and column delimiter arguments at the same time. The comma is the column delimiter and parses the numbers 1, 2 and 3 across three columns. The semicolon (:) is the row delimiter, forcing the array to the next row. Then, the commas between 5 and 6 parse those two values across the next two columns.

It’s actually very simple, although it solves a complex problem.

TEXTSPLIT() is versatile, flexible, and can solve some interesting parsing problems. To learn more about Excel’s other new string functions, you can read the following articles:

If you’re not familiar with dynamic array functions and the spill range, you might want to read this article: How to use the spill range in Excel.