Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Pulling Apart Cells.
by Allen Wyatt
(last updated July 4, 2015)
It's probably happened to you before: you get data for your worksheet, and one of the columns includes names. The only problem is, the names are all bunched together. For instance, the cell contains "Allen Wyatt," but you would rather have the first name in one column, and the last name in the neighboring column to the right. How do you pull the names apart?
You can easily use the Text to Columns feature in Excel to pull your data apart. Just follow these steps:
Figure 1. The beginning of the Convert Text to Columns Wizard.
Excel pulls apart the cells in your selected range, separating all the text at the delimiter you specified. Excel uses however many columns are necessary to hold the data.
If you don't want to spread your data completely across the columns, then you will need to use a macro. For instance, if a cell contains "John Davis, Esq.", then using the Text to Columns feature will result in the data being spread into three columns: the first containing "John", the second containing "Davis," (with the comma), and the third containing "Esq." If you would rather have the data split into two columns ("John" in one and "Davis, Esq." in the other, then the following macro will be helpful:
Sub PullApart() Dim Cell As Range Dim k As Integer For Each Cell In Selection k = InStr(Cell, " ") If k Then Cell.Offset(0, 1) = Mid(Cell, k + 1) Cell = Left(Cell, k - 1) End If Next End Sub
This macro examines each cell and leaves everything up to the first space in the selected cell, and moves everything after the space into the column to the right. The only "gottcha" with this macro is to make sure you have nothing in the column to the right of whatever cells you select when you run it.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9932) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Pulling Apart Cells.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel supports several types of dashes. This tip describes those different types and explains how to enter them in a cell.Discover More
Excel places limits on how much information you can enter into a cell and how much of that information it will display. This ...Discover More
If you type information into a workbook, you may want to make sure that what you type is always stored in uppercase. There is ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.