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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel allows you to easily paste information into a worksheet, including through simply dragging and dropping the ...Discover More
Excel makes it easy to copy and paste a range of cells. Easy, that is, unless the range isn't contiguous. If you have a ...Discover More
Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only ...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.