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.