We can make use a formula syntax based on Excel TRIM, SUBSTITUTE, MID, LEN and REPT functions for splitting text at specified delimiters (comma, pipe, space, etc.).

Since working with lots of text inside a single Excel column can be difficult, the **text delimiter Excel** tool is ideal for breaking up text into smaller, more manageable parts.

*Figure 1. of Excel Delimiter*

**Generic Formula**

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

The formula above operates by replacing a specific **delimiter Excel** with large amounts of space using the SUBSTITUTE and REPT functions, and then uses the MID function for extracting text related to a particular “nth occurrence” and finally, the TRIM function gets rid of any extra space.

**What is a Delimiter in Excel?**

An excel delimiter is a character sequence we used for specifying boundaries between different, independent regions on our worksheet. Examples of delimiters are the comma, pipe, and space characters, which act as text delimiters in a string of data.

**How to Delimit in Excel**

To separate text at specified points with arbitrary **delimiters in Excel **(space, comma, pipe, etc.) we are going to utilize a formula syntax combining the Excel TRIM, MID, SUBSTITUTE, REPT, and LEN functions.

- In our worksheet illustration shown below, our goal is to delimit the raw text string data from a cars dealer in column A:

* Figure 2. of Excel Delimiter*

- Our delimited Excel formula which we will enter into cell B2 above is as follows:

`=TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",LEN(A2))),(B2-1)*LEN(A2)+1,LEN(A2)))`

* Figure 3. of Excel Delimiter*

The raw data string in A2 has been **Excel Delimited** into smaller, more coherent pieces in the cells of columns B, C, D, and F.

- Modify and copy the delimiter formula in cell B2 above into other cells down the column to generate the desired results for other text strings in the RAW DATA column:

*Figure 4. of Excel Delimiter*

## Leave a Comment