Excel 2007 & 2010 Text to Columns and Concatenate Function

While Excel 2007 certainly has had its glitches, it has some new features in the Data ribbon that can save you time. Excel 2010 also contains these features.

If you are using Excel 2007, first make sure you have Service Pak 2 loaded. To check, click on the Office icon in the top left, Excel Options and then Resources. Then look for "SP2" in the About Microsoft Excel line. If you don't have SP2 loaded, click the Check for Updates box in the top right corner of the window.

Text to Columns Function to Separate Data

Text Data Example: Let's say you have customer first and last name in one cell but you want to alphabetize your list by last name.

Use the Excel 2007 or 2010 Text to Columns function to separate first and last name into two columns. Here is how it works:

Date Time Example: Here is an example of separating date time data. You may want to do this to run a pivot table by date.

Concatenate Function to Combine Data

If you want to combine fields together you can use the concatenate function. Lets say you have first and last name in separate fields but you want to combine them into one field to create a mailing list.

Click in an open cell like C1 and then click on Excel's function key

The Insert Function window will open. Select the Category as "Text" and within that category select Concatenate.

Next use Excel's window to define the formula. Excel will show you what the formula results will be as you define the formula. if you want a space between names, you have to define a blank space as part of the formula.

Click ok to see the results. Next copy the formula down to the remaining cells. Depending on how you plan on using the new combined data, you may want to do a Copy then Paste Special Values into a new column