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