Split text with delimiter

split text with delimiter

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

Later we feel the need to introduce advanced formulas.

Generic formula to split text with delimiter

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

In the example, the formula in B2 is:

=TRIM(MID(SUBSTITUTE($A$2,"*",REPT(" ",LEN($A$2))),(B$1-1)*LEN($A$2)+1,LEN($A$2)))

Examine the formula from the inside out

Let us see what is expected and the actual result in evaluating the formula. The main point of the nested formula:

  • replace the delimiters to spaces using SUBSTITUTE and REPT function
  • extract the nth occurrence of text using the MID function
  • remove the unwanted spaces using the TRIM function
Generic formula to split text with delimiter

Analyze the most important section of the below formula:

As first, replace the delimiters in the text to spaces.

=SUBSTITUTE(text,”*”,REPT(" ",LEN(text)))

After that, we’ll extract the nth substring using the MID function.

=MID(SUBSTITUTE($A$2,"*",REPT(" ",LEN($A$2))),(B$1-1)*LEN($A$2)+1,LEN($A$2)

Finally, we have to remove the unnecessary spaces using the TRIM function:

=TRIM(MID(SUBSTITUTE($A$2,"*",REPT(" ",LEN($A$2))),(B$1-1)*LEN($A$2)+1,LEN($A$2)))

A faster way to split text with delimiter: SUBSTRING

Speed up the work! In this section, we’ll show a faster way to split text which contains delimiters.

=SubString($A$3,”*”,N)

We need only add three arguments:

  • cell reference
  • type of the delimiter
  • the nth word, what we want to extract
A faster way to split text with delimiter - SUBSTRING

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

=SubString($A$3,”*”,1)

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

=TRIM(SubString($A$3,”*”,1))

Using built-in function: Text to Columns

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

You can extract a text into cells using various delimiter (tab, spaces, @ or what you want). Jump to Data Tab on the ribbon and select the Data Tools tab.

Related Formulas and Examples