Split text with delimiter

Generic-formula-to-split-text-with-delimiter

Learn how to split text with a delimiter (comma, space, or special characters) using the TRIM, MID, SUBSTITUTE, and LEN functions.

The article will show three possible ways to split text based on a delimiter. First, we will use regular string manipulation functions. After that, we’ll introduce the SUBSTRING user-defined function. Lastly, we’ll perform the task using the Text-to-Columns function. Today’s tutorial is a part of our definitive guide on Excel Formulas.

Generic formula to split text with delimiter

Here is the generic formula:

=TRIM(MID(SUBSTITUTE(A1,del,REPT(” “,LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

In the example, the formula in E3 is:

=TRIM(MID(SUBSTITUTE($B$3,””,REPT(” “,LEN($B$3))),(D3-1)LEN($B$3)+1,LEN($B$3)))

We will use a nested formula that contains the SUBSTITUTE, REPT, LEN, MID, and TRIM functions:

The formula replaces the delimiters to spaces using the SUBSTITUTE and REPT functions. Next, extract the nth occurrence of text using the MID function. Finally, it will remove unwanted spaces using the TRIM function.

formula-to-extract-words-from-a-text-string-based-on-a-delimiter

Evaluate the formula from the inside out.

  1. =SUBSTITUTE(text,”*”,REPT(” “,LEN(text))) replace the delimiters in the text with spaces.
  2. =MID(SUBSTITUTE(B3,””,REPT(” “,LEN(B3))),(D3-1)LEN(B3)+1,LEN(B3)) extract the nth substring using the MID function.
  3. =TRIM(MID(SUBSTITUTE(B3,””,REPT(” “,LEN(B3))),(D3-1)LEN(B3)+1,LEN(B3))) remove the unnecessary spaces using the TRIM function.

Split text with delimiter using the SUBSTRING function

You can reduce the formula length if you use the SUBSTRING function. Speed up the work with a user-defined function! In this section, we’ll show a faster way to split text which contains delimiters. Our UDF add-in, DataFX, supports the SUBSTRING function.

Syntax:

=SUBSTRING($B$3,”*”,N)

Arguments:

  • cell reference = B3
  • type of the delimiter = “*”
  • N = the nth word is what we want to extract
how-to-use-the-substring-function-to-split-text-with-delimiter

For example, if we want to split the second word from full text, apply the below formula in cell B3:

=SUBSTRING(B3,”*”,2)

Finally, we’ll use the TRIM function to get rid of spaces.

=TRIM(SUBSTRING(B3,”*”,1))

Using the built-in Excel tool: Text to Columns

If you have a small data set, we recommend using the built-in Excel feature Text to Columns. This function is a real Swiss knife.

You can extract text into cells using various delimiters (tab, spaces, @, or whatever you want). For example, jump to the Data Tab on the ribbon and locate the Data Tools tab. Then, click on the Text to Columns icon.

text-to-columns-function

After clicking the icon, the “Text-to-Columns” wizard will appear. First, choose “Delimiter”, then click next. On the second page, select “Other” and type the delimiter, in this case: “*”.

Click “Finish” to separate the entire text string based on the delimiter.

use-the-text-to-columns-delimiter-feature