Excel Delimiter

  • Post author:
  • Post category:Uncategorized

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.

  1. 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

  1. 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.

  1. 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