Excel Functions and Formulas Sample #1, Split Strings Based on Delimiter

Aug 12, 2015 by azurous in Download

In this article I will provide an example on how you could split a string based on a delimiter character using excel formulas and functions. You can download the workbook for this article here.

Jump To:

Function Used in this Example
Summary
Implementation

Contents

Functions Used in this Example:

The following functions have been used to make this example:

–

Summary:

Using the functions and formulas in excel in this sample I have created a worksheet which gets two text strings as input. The main text and a delimeter. The main text is split at the locations the delimeter is found and printed in column D. I have brought some examples below:

In the example below I have used the following input data:

Cell A2: “This is the text which will be split based on the delimiter in cell B2″

Cell B2: ” ” (Space character)

Result:

Lets say we change the contect of the cells to:

Cell A2: “David,Mark,John,Ellon,Emma,Patrick,Kevin,James”

Cell B2: “,” (comma character)

Result:

Another example:

Cell A2: “somerandomtextosplitbasedonsomerandomcharacters”

Cell B2: “ra”

Result:

–

Implementation:

So how is this achieved?

Step 1, Indice Column:

In the first step we need to find the indices where the text in cell B2 occurs in the text in cell A2. I have explained how this could be achieved in Excel Formulas and Function, Finding Text Within Another Text, Find(). Implement the indice column in column C:

The indice column (column C) shows us that the character in cell B2 (“,”) has occured in the indices 6, 11, 16, 22, 27, 35 and 41 of the text in cell A2.

Step 2, determine if the end of the text string has been reached:

In this step we will create a column to check if the end of the text string has been reached or not. This will be implement in column E. Write the following formula in cell E3 and copy/drag it down to cover as many rows as needed:

=IF(C3="", 1, 0)

The formula above checks if the content of cell C3 is empty or not. If it’s empty it would mean the end of the text string has been reached which in that case the value “1” will be printed in cell E3. If cell C3 is not empty then the value “0” will be printed in cell E3. For more information about the content of column C please see Excel Formulas and Function, Finding Text Within Another Text, Find():

Result:

Step 3, Return the first string:

Write the following formula in cell C3:

=IF(E3=0, LEFT(A2,C3-1), A2)

The formula above is an IF() function with 3 parts:

=IF(Logical Test, Value if True, Value if False)

Logical Test: The logical test is the first parameter passed to the IF() function:

=IF(E3=0, LEFT(A2,C3-1), A2)

It checks if the value in cell E3 is “0” or not. If the value in cell E3 is zero that would mean the value in cell C3 is zero, Step 2. The value in step 2 being zero would mean the end of the text string has still not been reached Excel Formulas and Function, Finding Text Within Another Text, Find().

If the value in cell E3 wasn’t equal to “0” it would mean the end of the text string has been reached. What would having a none “0” value in cell E3 mean for the first row? It would mean that the text in cell B2 wasn’t found in cell A2.

Value if True:

=IF(E3=0, LEFT(A2,C3-1), A2)

In case the logical test returns True, meaning that the text in cell B2 has been found in the text in cell A2, then the formual below will be executed:

LEFT(A2,C3-1)

You can find more information about the LEFT() function in Excel Formulas and Function, Finding Text Within Another Text, Find(). What it does is it returns C3-1 number of characters from the beginning of the text in cell A2. So why C3-1? We don’t want the delmiter to appear in the resulting text.

Value if False:

=IF(E3=0, LEFT(A2,C3-1), A2)

In case the logical test returns False, that would mean the text in cell B2 was not found in the text in cell A2. In this case we would want the entire text in cell A2 to display in cell D3. This is achieved by the Value if False part of the IF() function.

Result:

Step 4, Return the next string part:

Write the following formula in cell D4:

=IF(E4=0, MID(A$2,C3+1,C4-C3-1), IF(E3 <> 1, RIGHT(A$2, LEN(A$2)-C3),""))

Again this is an IF() function with 3 parts:

=IF(Logical Test, Value if True, Value if False)

Logical Test:

=IF(E4=0, MID(A$2,C3+1,C4-C3-1), IF(E3 <> 1, RIGHT(A$2, LEN(A$2)-C3),""))

Similar to Step 3.

Value if True:

=IF(E4=0, MID(A$2,C3+1,C4-C3-1), IF(E3 <> 1, RIGHT(A$2, LEN(A$2)-C3),""))

In case the logical test returns True that would mean the end of the text string has not been reached. Therefore the code below executes:

MID(A$2,C3+1,C4-C3-1)

For more information about the MID() function please see Excel Functions Get Substring, MID(), LEFT(), RIGHT(). The formula above returns C4-C3-1 amount of characters from the text in cell A$2, starting from the index C3+1.

Why did I use A$2 instead of A2? In the next step the formula in this cell the will be copied/dragged down to the other cells. We want the text to be extracted from cell A2 for all rows.
Why C3+1? We don’t want the delimiter to appear in the extracted text.
What is C4-C3-1? It is the number of characters between two adjacent occurrences of the delimiter.

Value if False:

=IF(E4=0, MID(A$2,C3+1,C4-C3-1), IF(E3 <> 1,RIGHT(A$2, LEN(A$2)-C3),""))

In case the logical test returns False that would mean the end of the text string has been reached. Therefore the code below will execute:

IF(E3 <> 1, RIGHT(A$2, LEN(A$2)-C3),"")

The code above is another IF statement. The logical test checks if the value in E3 is not “1”. If E3 is “1” it would mean that the previous row was the last row, Step 2, Determine if the end of the text string has been reached. If the logical test return true that would mean the end of the text string has not been reached. Therefore the code below will execute:

RIGHT(A$2, LEN(A$2)-C3)

For more information about the RIGHT() function please see Excel Functions Get Substring, MID(), LEFT(), RIGHT(). The formula above returns from the text in cell A$2, C3+1 amount of characters starting from the end of the text string.

If the logical test returns False, that would mean mean the end of the text string has already been reached in the previous. Therefore an empty cell is printed (“”):

Result:

Step 5, Copy/Drag formula from step 4:

Copy or drag the formula from step 4 down to cover the rest of the cells in the column:
Result:

Step 6, Hide the uncessary columns:

Hide columns C and E: Result:

You can download the workbook for this article here.