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 Pack 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:

  1. Click and drag over your data to select it
  2. Next, click on the Data tab on Excel's ribbon menu, then Text to Columns
  3. Excel
  4. The Text to Columns Wizard will open. In step one, define whether your data is separated by a space, comma, tab or some other character (delimiter) or if one of the fields has a fixed width. In this case, we will select "delimited" since first and last name are separated by a space.
    Excel
  5. In step 2, you can define what the delimiter is:
  6. Excel
  7. In step 3, you can set the format for the new data.
  8. Excel
  9. Click finish and your data is separated into two columns. Now we can sort on last name in column B.


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.

  1. Click and drag over the data to select it.
  2. In step 1, select fixed width since the date is always 8 characters.
  3. In step 2, Excel will choose the break point for you based on your data, or you can select your own.
  4. Excel
  5. In step 3, set the format for the new data then click finish. Date and time are now in separate columns.

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.

Excel

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.

Excel

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

Excel

Create these charts and diagrams yourself in just seconds using the QI Macros for Excel...
Try It Now!


Excelerate Your Projects Download Free QI Macros Trial Buy QI Macros for Excel Now