Excel Formulas to Split the Text into Delimiter ~ Useful Guide!!

If you have a column list of data, and you want to split them into several columns by a specific delimiter, how can you split them in Excel? Today I will introduce some easy tricks to split the text into delimiter in Excel. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

Use the below formula to split the text into delimiter in Excel.

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(” “,LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Syntax Explanations:

TRIM – In Excel, the TRIM function removes the extra spaces from the text and there is no space at the start or end of the text string.
MID – This function helps to return the smallest numeric value from the range of input values. Read more on the MID function.
SUBSTITUTE – The Excel SUBSTITUTE function can replace existing text with new text in a text string when you want to replace text based on its content, not position.
REPT – It repeats characters a given number of times. Read more on the REPT function.
LEN – This function returns the length of a given text string as the number of characters. Read more on the LEN function.
A1 – It represents the input value.
Plus operator (+) – This symbol is used to add the values.
Multiplication (*) – In this symbol will multiply any two values or numbers.
Minus Operator (-) – This symbol will help to subtract any two values.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Refer to the below example image.

Here we will enter the input values in Column B.
Now we are going to split the text into a given delimiter.

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key, you can get the result as shown below.

Wrap-Up:

From this short tutorial, you can get some clarification on how to split the text into delimiter in Excel. Hope you like this article. If you have any issues regarding this article or any unresolved queries , please comment in the comment box below. We will assist you.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **