Excel Split Cell by Delimiter Formula

This tutorial will show you how to split a cell by delimiter with the help of a formula in Excel. A delimiter is a character that separates chunks of data within the text string.

Before beginning the session, let’s get to know about today’s example workbook.

The base of our example will be students related data (Name, ID, Course, City). Using these data we will show different methods that work in different circumstances.

Examples of all the methods will be stored in separate sheets.

Practice Workbook

You are welcome to download the workbook from the link below.

Excel Feature to Split Cell

Excel has the feature within it to split cells. You will find it inside the options of Data Tab.

First, select the cell or column (more often you need to select an entire column).

Then explore the Data tab. Here within the Data Tools section you will find an option called Text to Columns. Click that.

A dialog box will appear in front of you. It’s usual that you will need to split cells by delimiter, so check the Delimited option and Click Next.

Then you will find an interface containing several delimiters. Select your preferred one or you can input your own delimiter as well.

Once you select the delimiter, you will see the result look like at the bottom of the box. Click Next then.

In this example we’ve selected a comma here, since our values were comma separated.

After clicking Next you will find the options to choose the type of your value and click Finish. You will get the separated value.

For the time being, we are keeping this as General (by default). The format showing in the image below was produced after making some formation.

Useful Functions to Split Cells

There are few functions that will help us form the formula to split cells. Let’s get to know about them ahead of the beginning of the formula section.

1. SEARCH Function

SEARCH function returns the location of one text string inside another.

Syntax of SEARCH function is

find_text: The text you want to find

within_text: The text to search within

start_num: Starting position in the text to search. By default 1.

SEARCH function returns the starting position of find_text inside within_text.

You can explore Microsoft Support site for more details.

2. LEFT Function

LEFT returns the first character or characters in a text string, based on the number of characters you specify.

Syntax for LEFT function is

text: The text from which to extract characters.

num_chars: The number of characters to extract, starting on the left side of text. You leave this field. By default it’s 1.

You can explore Microsoft Support site for more details.

3. MID Function 

Similar to the LEFT function, MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Syntax of MID function is

text: The text to extract from.

start_num: The location of the first character to extract

num_chars: The number of characters to extract

You can explore Microsoft Support site for more details.

4. RIGHT Function

RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

text: The text from which to extract characters on the right

num_chars: The number of characters to extract, starting on the right

You can explore Microsoft Support site for more details.

5. LEN Function

LEN function returns the length of a given text.

text: The text for which to calculate length.

You can explore Microsoft Support site for more details.

6. SUBSTITUTE Function

SUBSTITUTE function replaces any text in a given string by matching.

text: The text you want to change.

old_text: The text to replace.

new_text: The text to replace with.

instance: The instance to replace. It is an optional field. If not provided, all instances are replaced.

You can explore Microsoft Support site for more details.

Ways to Split Cell by Delimiter using Formula

1. Split comma – dash or other delimiter separated text

First thing you need to do for splitting by delimiter is locate the delimiter itself. Once you locate the delimiter you can easily split from the either sides of the delimiter.

We will use the SEARCH function to locate the delimiter, then we will extract the values from the text using the LEFT, MID or RIGHT function.

Let’s begin. Since the LEFT function has two parameters, text and number of characters. We will insert the text as we know our text value. For the number of characters, we will use the SEARCH function.

Formula syntax will be:

Write it in the Excel.

In the example our delimiter is the hyphen ‘-‘.

You may wonder why we have subtracted 1? 

The SEARCH function would have provided us the position of a hyphen. Now we don’t need the hyphen itself, we need to extract ahead of the hyphen.

Now Let’s write for the middle value.

Our formula will be something like the following

Here at the placeholder for start_num of MID function, we have set

It will start from the character next to the hyphen.

In place of number of characters

This indicates the number of characters within two hyphen delimiters.

Write it in Excel now.

It gave the result we had wanted.

Now for the right side of the text our formula will be

LEN function returns the total length of the string, from which we subtract the position of the last hyphen. The difference is the number of characters after the last hyphen, and the RIGHT function extracts them.

Use the appropriate formula for the rest of the values.

You can split columns by any other character in a similar fashion. All you need to do is to replace “–“ with your required delimiter.

Read More: Excel Formula to Split String by Comma (5 Examples)

2. Split Text by Line Break

To split string by line break we will use a similar formula to the previous section. One extra function we need to add to our previous formulas. The function is CHAR.

This CHAR function will supply the line break character.

To extract the Top most value our formula will be

10 is the ASCII code for line. We are providing 10 within CHAR to search line break.

For the middle value

For the bottom value

Now use appropriate formula for the rest of the values

Read More: How to Split Cells in Excel (5 Easy Tricks)

3. Split ‘text+number’ String Pattern

In the section we will see how to split a text having a character string followed by a number.

For simplicity we have brought some changes to our sheets (No worries all the sheets will be in the workbook).

In our example we have Student Name and ID together in a column, and split them into two different columns.

To split text followed by a number format string we need to find out the number first, then by the help of that extracted number we can extract text.

To extract the numbers, we need to look for every possible number from 0 to 9 within our string.

Then get the numbers total and return the number of characters from the end of the string.

Our formula will be

Within SUBSTITUTE we are replacing numbers with space and counting them using LEN.

To extract the text value, now we need to use LEFT function ang in the placeholder for number of characters provide the Total length of the cell – length of digits within that

For the rest of the values exercise the same formula.

Read More: Excel VBA: Split String by Number of Characters (2 Easy Methods)

4. Split ‘number+text’ String Pattern

If you have understood the method for splitting a ‘text+number‘, then hopefully, you have begun to imagine a way to split a string of numbers followed by text format.

The approach will be the same as earlier, just one change you will notice.

Now the number is at the left of our text, so we need to use the LEFT function to fetch number and for character text we will use RIGHT function.

So, our formula for extracting the number will be

And for character text will be

Use the necessary formula to fill out the rest of the values.

5. Split Date from string

To split the date from your text you can use the formula below

As the date value at the end of the sting so we have traversed a number of instances so that month, date and year can be abstracted. If your target value requires more text to drive, you can extract them by changing number of instances.

This formula will only be useful when you have a date at the end of your text string.

Read More: Excel VBA: Split String into Cells (4 Useful Applications)

Conclusion

That’s all for today. We have tried listing several ways to split cell by delimiter using formula. Hope this will be helpful. Feel free to comment if anything seems difficult to understand. You can also let us know any other ways in case we have missed it here.

Further Readings

• How to Split One Cell into Two in Excel (5 Useful Methods)
• How to split a single cell in half in Excel (diagonally & horizontally)
• VBA to Split String into Multiple Columns in Excel (2 Ways)
• Excel VBA: Split String by Character (6 Useful Examples)
• How to Make Two Lines in One Cell in Excel (4 Methods)