Using TEXTSPLIT to dissect Excel text strings

Q. I have used the Excel functions LEFT, MID, and RIGHT to dissect cells. However, I have some spreadsheets where each piece of information is a different length and uses different delimiters. Is there an easy way to dissect these types of text strings?

A. TEXTSPLIT is a very useful function for breaking down text cells that contain different lengths of text and delimiters. It can be used in several ways, and we’ll review some examples of how to use it.

If you would like to follow along with the examples, you can download this Excel workbook and view the video at the end of this item.

Note that the following content is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

Extracting, parsing, or splitting data using TEXTSPLIT is an easy process. Occasionally, we receive data in formats that are unreadable or unusable. To illustrate, look at the screenshot below. We have received all the data for our customers in a single cell, with a comma separating each piece of information.

To transform data into a usable format, we can use TEXTSPLIT.

TEXTSPLIT has six arguments, but only two are required. Let’s focus on those two. The first argument is “Text,” which is the text string we want to split. The second argument is Col_delimiter, which is the character(s) we want to use to split the text string into separate columns. In this case, we will use the formula in cell K2, as shown in the screenshot at the top of the next page. The formula references cell A2 for the Text argument, which contains the text string we want to extract information from. We will use “, ” as the Col_delimiter because we want to split the text string each time there is a comma followed by a space.

It’s important to note the formula was initially created in cell A2 and then automatically filled across columns L through Q. Although you can see the customer’s address in cell L2, the formula is grayed out. This is known as a ghosted formula, meaning it was carried over from the formula entered in cell K2. If you wish to modify the formula for this customer, you will need to make those changes in cell K2. After making the necessary changes, copy the formula down for all customers.

Consider another scenario where we encounter two types of delimiters. As you can see in the screenshot below, we have a dataset in which all our customer information is contained in a single cell, with each piece of data separated by either a comma and a space or a semicolon and a space.

We can again use TEXTSPLIT to separate this information. You can enter multiple delimiters into the argument Col_delimiter. You must put curly brackets around the entire set of delimiters, separate each delimiter with a comma, and put quotes around each delimiter in the set. In this example, the two delimiters are a comma and a space and a semicolon and a space.

Let’s look at another example where data appears in a single text string, and we want it to be shown in multiple columns and rows. See the screenshot below. I would like our employees’ names to appear in column A and their sales amounts in column B.

We can again use TEXTSPLIT to complete this task. To do so, we need to include one of the optional arguments: Row_delimiter. This argument is the character(s) we want to use to split the text string into rows. See the formula for this example in cell A3 in the screenshot at the top of the next page. The formula references cell A1 for the “Text” argument, which contains the text string from which we want to extract information. We will use “-” as the Col_delimiter because we want to split the text string into columns each time a dash is used. Each time a comma and a space are used, we want to split the text down the rows, so we use “, ” as the Row_delimiter.

TEXTSPLIT can also be used in conjunction with other functions, which makes it even more useful. Give TEXTSPLIT a try when you need to separate out information in a text string.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.