Home » Free Excel Tips » Text to Columns

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.

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

   

4. In step 2, you can define what the delimiter is:



5. In step 3, you can set the format for the new data:



6. 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. 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.

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:

---

Isn't it Time to Upgrade Your Excel Charting Skills?

Stop trying to make your spreadsheets easy to read. Nobody wants to read your spreadsheet!
And stop using primitive line, pie and bar charts.
Isn't it time to graduate to Smart Charts: Control Charts, Pareto charts and Histograms?

Smart Charts Included in QI Macros for Excel