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.