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. Last but not least, 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.
Evaluate the formula from the inside out.
First, replace the delimiters in the text with spaces.
=SUBSTITUTE(text,”*”,REPT(" ",LEN(text)))Formula to extract the nth substring using the MID function:
=MID(SUBSTITUTE(B3,"*",REPT(" ",LEN(B3))),(D3-1)*LEN(B3)+1,LEN(B3))Remove the unnecessary spaces using the TRIM function:
=TRIM(MID(SUBSTITUTE(B3,"*",REPT(" ",LEN(B3))),(D3-1)*LEN(B3)+1,LEN(B3)))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
For example, if we want to split text 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 you use the built-in Excel feature, Text to Columns. The 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 Data Tab on the ribbon and locate the Data Tools tab. Then, click on the Text to Columns icon.
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.
