Blog

Have you ever needed to break up a block of text into separate parts based on a delimiter, like a comma, space, or period? The TEXTSPLIT function in Excel does just that! It’s a powerful tool for splitting a string of text into individual components, making it easier to work with data that’s lumped together. Whether you’re splitting names, product codes, or any text with a common separator, TEXTSPLIT gets the job done in seconds.

Syntax

Here's the syntax for the TEXTSPLIT function, which lets you split a string into multiple parts based on a delimiter:

• text: The text string or cell reference you want to split.
• delimiter: The character(s) used to split the text (e.g., space, comma, dash).
• [ignore_empty]: (Optional) A boolean to decide whether to ignore empty cells in the split.
• [match_mode]: (Optional) Specifies whether the case matters.
• [pad_width]: (Optional) Allows padding if the result is shorter than the array specified.

Example

Let’s say you have a list of products with multiple attributes (such as brand, model, and type) all separated by commas, and you want to split this data into individual columns.

In this example, the TEXTSPLIT function splits the product information into its brand, model, and type using the comma , as the delimiter.

Practice Exercise 1

Task: You have a list of full addresses where each part (street, city, state, zip) is separated by a comma. Your task is to use TEXTSPLIT to break the address into its components and display them in separate columns.

Hint: Use the comma as the delimiter and make sure to split each part of the address into separate columns: street, city, state, and ZIP code.

Practice Exercise 2

Task: You are working with a dataset of customer orders where each product is separated by a vertical bar (|). Split each order into individual products using the TEXTSPLIT function and display them in separate columns.

Hint: Use the vertical bar | as the delimiter to split each order into separate products.

Solution to Exercise 1

Splitting the full address into components:

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

Each part of the address (Street, City, State, ZIP) is separated by commas and placed into separate columns.

Solution to Exercise 2

Splitting customer orders into individual products:

=TEXTSPLIT(A2, "|", TRUE)
=TEXTSPLIT(A3, "|", TRUE)
=TEXTSPLIT(A4, "|", TRUE)

The vertical bar | acts as the delimiter to split each product in the order into a separate column.

And that’s how easy it is to split text into different parts with the TEXTSPLIT function in Excel! Whether you’re working with addresses, product orders, or any other kind of data, this function is your go-to tool for efficient text parsing.