• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Separate Text in Excel

June 18, 2021 by Andrew Childress
Separate text across columns in Excel

When you’re working with data in Excel, you may find yourself using cells containing multiple values. Here's how to 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)

Separate text in Excel

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.

Separate text in cells Excel

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.

Separate text across columns in Excel

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.

Text to columns in Excel

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.

How to separate text in Excel

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.

Separating text in Excel

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.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Combine two columns ExcelCombine Two Columns in Excel
Next Post:HLOOKUP in ExcelHlookups in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top