Excel Text to Columns and Concatenate Functions

Available in Excel 2013-2021, Office 365

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.

Customer data setup

Use Excel's 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:
  10. after text to columns


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. Data time setup
  3. In step 1, select fixed width since the date is always 8 characters:

    text to columns fixed width step 1
  4. In step 2, Excel will choose the break point for you based on your data, or you can select your own:

    Excel text to columns fixed width step 2
  5. In step 3, set the format for the new data then click finish. Date and time are now in separate columns:

    text to columns fixed width step 3

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:

excels 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


Stop using old technology!

Upgrade Your Excel and Data Analysis Skills to Smart Charts Using QI Macros.

Track Data Over Time

Primitive Chart
line graph
Line Graph
Smart Chart
control chart
Control Chart

Compare Categories

Primitive Chart
pie chart
Pie Chart
Smart Chart
pareto chart
Pareto Chart

Analyze Variation

Primitive Chart
bar or column chart
Bar or Column Chart
Smart Chart
histogram
Histogram

QI Macros add-in for Excel makes creating smart charts a snap.