Explanation

TEXTSPLIT is a function that splits a text into two or more cells, based on a delimiter. 

Example

Let’s start with a simple example where we split the text after each whitespace (” “):

(For those of you who didn’t recognize this line, please pay a visit here and take a listen to what is probably one of the greatest songs by one of the greatest bands ever 🙂 )

Syntax

text – What’s the text we want to split? We can either type a text surrounded by quotes (“”) or refer to a cell that contains that text

col_delimiter – What’s the character (or characters – it could even be an entire word…) that we want to use as a delimiter? Once such a delimiter is found in the text, the rest of the text will shift to the next column (or columns, depending on how many such delimiters were found). 

The next arguments are optional – You can leave these blank and the function will work alright. If you use these, they will add extra functionality to TEXTSPLIT. Let’s go over these options 🙂 

[row_delimiter] – So far, we saw an example where our text is split into multiple columns – yet they were still in the same row. But what if we want to split our text into rows as well? In this case, we can use the row_delimiter argument! 

Let’s try this using our previous example – but now, let’s ask Excel to split the text into words, where each word is in a new row!

Notice that we left the col_delimiter argument blank this time, as we do not want to split our text into columns at all – only into rows! 

Can we split text to both rows and columns at the same time? Yes! Keep reading to see an example 🙂

[ignore_empty] – This argument determines whether empty values between delimiters should be ignored or not. The default (0) is to not ignore. To ignore empty values, type 1. 

Example:

[match_mode] – Do we want the delimiter search to be case-sensitive or not? By default (if you type 0 or keep this argument blank) it’s case-sensitive. To set the function to be case-insensitive, type 1. 

Let’s see an example of the difference between a case-sensitive vs. case-insensitive search of the text “the “:

When the search is case-insensitive, “The ” with a capital T is considered the same as “the “, therefore we find one more delimiter, and one more row is created. 

[pad_with] – The value to use for padding. The default value is #N/A. This argument should be used when splitting text into both rows and columns at the same time, as there might be situations where there’s not enough data to fill some of the cells in the rows/columns.

To make it simpler, let’s see an example – Let’s try to split the sentence from the first example into both columns and rows. Let’s use whitespace (” “) as the column delimiter, and comma followed by whitespace (“, “) as the rows delimiter, and see what happens:

Have you noticed the #N/A error in the 5th column in the first row? This happens because there are only 4 words in the first sentence (Welcome to the jungle), yet after the comma & space, there are 5 words (we’ve got fun and games). So the missing word is represented as #N/A. 

We can solve this by telling Excel to pad with nothing (“”): 

Practice TEXTSPLIT

Let’s practice and learn how to use TEXTSPLIT:

 

Using TEXTSPLIT to extract a letter from each word in the cell

One crazy-cool usage for TEXTSPLIT (combined with the legendary TEXTJOIN) is for situations where you’d like to extract a letter (or a number of letters!) from each word in the cell. 

Let’s say that you have a list of names, and you would like to get the initials of each name. 

For example – John Doe = J.D

You can use TEXTSPLIT to first split the cell into separate words, then use the LEFT function to extract the first letter, and finally – use the TEXTJOIN function to join the first letters together!

Let’s see an example:

Here’s the formula we used, if you wish to copy it to your worksheet (Replace A2 with your cell):

=TEXTJOIN(“.”,,LEFT(TEXTSPLIT(A2, ” “),1))

Want to try it out? Go ahead: