Three Ways to Split Delimited Strings in Excel

Ever received a data set where multiple pieces of information are in the same column? For example, say you inherit a spreadsheet with a column that contains the First and Last names of all staff in a department separated by a period mark ( . ). But what you want is to have two separate columns: 

1. One that lists staff members' first names; and a

2. Second column showing staff members' last names.

Luckily, Excel makes it easy to split the string into two parts. In this post, I will show you three ways to separate data from one column into two columns in Excel.

Delimiters

Splitting strings in Excel is an easy task. However, to properly extract data from columns that contain multiple pieces of information, you must know what delimiter to use. A delimiter is a fancy way of talking about characters or symbols that separate text strings. Examples of delimiters include white space and punctuations like commas or periods.

As an example, consider the following string:

Karin.Walborn

The period ( . ) between Karin and Walborn is a delimiter that separates the staff person's First Name and Last Name. First Name and Last Name can be considered two separate fields (or variables); for analysis, you probably want these names in two different columns. 

So, what are some options for splitting these text strings into two columns in Excel? Read on to learn more about my favorite methods.

Splitting strings

Option #1: Text to Columns Wizard

One option for splitting data from one column into two (or more) columns is to use Excel's Text to Columns Wizard.  

Step 1: Select the column you want to split:

Step 2: Select the Data tab on the Excel Ribbon and then click the Text to Column button.

Step 3: When the Convert Text to Columns Wizard dialog box pops up on your screen, make sure the Delimited radio box is selected and then click Next.

Step 5: In Step 2 of the Wizard, select the Other option, enter a period ( . ) in the provided text box, and then click Finish.

And voila!

See the 5 steps in action below:

Not too bad, right?  

If you are looking for formula alternatives to the point-and-click method, keep reading!

Option #2: LEFT/RIGHT + FIND + LEN

Another popular choice is to use a function. For our purposes, we are going to write two formulas: one that will extract First Names and a second for Last Names. To accomplish this task, we will use four of Excel's built-in functions:

• LEFT

• RIGHT

• FIND; and

• LEN

The LEFT function allows you to extract elements of a string starting with the leftmost character. The function has two arguments:

1. Text: the text you want to extract (usually the location of a cell in a spreadsheet); and

2. Num_chars: the number of characters to extract.

The RIGHT function allows you to extract elements of a string starting with the rightmost character. The function has two arguments:

1. Text: the text you want to extract (usually the location of a cell in a spreadsheet); and

2. Num_chars: the number of characters to extract.

The FIND function allows you to locate a character or text string within another string of text and returns the position of the found string. The function has two arguments:

1. Find_text: the character or text string you want to locate; and

2. Within_text: the text string you will be searching in (usually the location of a cell in a spreadsheet).

The LEN function returns the number of characters in a string of text. (The function has a single argument, text.)

So, if I wanted to extract the FIRST character of the string of text located in cell A1, I would enter the following in cell B1:

=LEFT(A1, 1)

Whereas, if I wanted to return the LAST character of the string of text located in cell A1, I would enter:

=RIGHT(A1, 1)

 in cell B1.

On the other hand, if you wanted to locate WHERE in the string of text a punctuation like a period mark can be found, enter:

=FIND(".", A1)

 (The number 10 is returned because the period mark is the 10th character in the string of text.)

(The number 10 is returned because the period mark is the 10th character in the string of text.)

Finally, if you wanted to count the number of characters present in the string of text located in cell A1, enter:

=LEN(A1)

Okay, now that we know what these functions mean, let's get to it!  

Toggle to the second worksheet titled left.right.find. First, we are going to extract First Names and place them in Column B.

Step 1: In Cell B1, enter:

=LEFT(A1,(FIND(".", A1)-1))

This formula tells Excel that you want to use the LEFT function to extract all characters to the left of the period mark in cell A1. Note how we are subtracting one from FIND. Why? Remember, FIND returns the nth location of the character or text string you wish to locate, whereas LEFT requires you to specify how many characters to extract. We do not want to return the First Name AND the period mark, so we subtract one.

Next, apply the formula to the rest of the column by double-clicking the fill handle (small black plus sign ( + ))

Okay, now, onto last names!

Step 2: In Cell C1, enter:

=RIGHT(A1, LEN(A1)-FIND(".", A1))

This formula is a little bit more complicated because we need to know WHERE the period mark is in the string of text and how long the string of text is. Why? Well, the FIND function will return the location of a character or text string within another string of text, but the RIGHT function requires that you specify how many characters to extract. You would have to manually count how many characters each text string has to determine the latter number. I prefer to let the LEN function do the heavy lifting for me.

Next, apply the formula to the rest of the column by double-clicking the fill handle (small black plus sign ( + ))

Option #3: TEXTSPLIT

A final option for splitting data is to use Excel's built-in TEXTSPLIT function. TEXTSPLIT is a new(er) addition to the Excel function family and is only available to Microsoft 365 subscribers or users of Excel for the web. TEXTSPLIT is the function version of Excel's Text to Columns Wizard. When splitting across columns, the formula only requires two arguments:

1. text: the text you want to separate; and

2. col_delimiter: The character(s) or symbol(s) that separate the text strings

Toggle over to the last worksheet titled textsplit and enter the following into cell B1

=TEXTSPLIT(A1, ".")

The best part? When you enter the formula into cell B1, it automatically places everything to the right of the period mark into cell C1:

Finally, apply the formula to the rest of the column by dragging down the fill handle (small black plus sign ( + )).

The next time you need to split a single-column, delimited text string into multiple columns, try one of my methods.

Do you have a favorite method? Use the comments to share your thoughts.