Separate Text in Excel

When you’re working with data in Excel, you may find yourself using cells containing multiple values. For example, you may see first and last names in a single cell. But sometimes you’ll want to separate text so that distinct values populate individual cells. Fortunately, Excel offers multiple easy ways to do this. Let’s learn how to separate text in Microsoft Excel.

How to Separate Text in Excel (With Formulas)

Imagine a list of names, contained in column A. Someone sent you the file, but you’d rather have the first names and last names in their own separate columns. Of course, if you only have a few cells of text to separate, you could simply retype the data. But this would become overwhelming. Luckily, there are better ways.

First, you can use formulas to isolate individual names. Begin by clicking into cell B2. A basic technique is to use the LEFT and RIGHT functions in Excel. These look in an individual cell, and return a given number of values that appear in either the left or right sides of the cell.

In cell B2, type an = sign. This tells Excel that you’re inserting a formula into the cell. Then, type LEFT(. Your formula now reads:

=LEFT(

Now, you need to count the number of characters in the first name. Here, it’s 4. Click into cell A2, type a comma, then type 4. Close the parentheses by typing ), completing your formula as follows:

=LEFT(A2,4)

Hit Enter, and you’ll see Excel separate the first name: cell B2 now reads John.

In cell C2, insert your RIGHT function, this time counting the number of letters in the last name:

=RIGHT(A2,5)

Hit Enter, and you’ll see Excel separate the last name: cell C2 now reads Smith.

This has a key limitation; since you specified character counts, this technique won’t let you copy formulas downward unless each individual text element has the same number of characters.

Fortunately, Excel has a second option just for you.

How to Separate Text in Excel (With Text to Columns)

Now, imagine a list of data with different lengths. The LEFT and RIGHT formulas would be difficult to utilize, since they aren’t copyable. This is the perfect time to use Excel’s powerful Text to Columns Wizard. Text to Columns is a smart tool that analyzes selected data, and gives you precise control over the process of separating text in Excel.

To start using it, click and drag your cursor to select your list of text that you want to separate. In this example, you’ll find that range in cells A2:A5.

Then, go to the Data tab, found on Excel’s ribbon. Near the center, you’ll see the Text to Columns button. Go ahead and click on it.

Excel launches the Convert Text to Columns Wizard. Here, you decide if data is Delimited or Fixed Width. Delimited data is data separated by spaces, commas, and so forth. It’s the default option, and the correct one here. Click Next.

Excel then asks you to identify the delimiters: tabs, commas, spaces, and more. In this case, the delimiter is a space, so click the Space checkbox. Also, ensure that no other boxes are checked. Watch in the box below: you’ll see Excel previewing how your text will separate based on your delimiter selection.

Click Next once again, and then click Finish. You’ll see that Excel instantly separates the selected range of text. The first name remains in the original column, while the second name appears in column B.

As you can see, it’s easy to separate text in Microsoft Excel. It’s a great way to sort and filter data in a way that works best for your project needs.