# 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

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

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.