Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Delimiter Excel Excel Delimiter – Excelchat

Excel Delimiter – Excelchat

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

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc