Distributing data across columns with Text to Columns in Excel

In Excel, you can take a cell and split its text across columns in a number of ways. The easiest of these is to use the Text to Columns command under the Data menu in Excel.

For example, if you have a cell that contain both first and last names, you can use the Text to Columns command to split the data in each cell across multiple columns using a delimiter, a special character that indicates at what point data should be split. In this example, we will use a space as the delimiter.

To split text in a cell across columns:

1. Select the cells you wish to affect.
2. In the Data menu, click Text to Columns.
3. In the Wizard, select Delimiter, then click Next.
4. In the Delimiters area, check Space, then click Finish.

Before Text to Columns

After Text to Columns

Paste the resulting values, not the formula with Paste Special in Excel

Occasionally, you may need to use a formula to change the way data appears. And, it is often handy to be able to extract the resulting value of a formula, but not the formula itself.

For example, you can use a formula to remove extra spaces from data. If you then wanted to split the resulting values across columns, you would need to extract the values because you can only split text across columns, not formulas.

To paste values

1. Select the cell(s) containing the formula(s).


2. Press CTRL+C to copy.


3. With the cell still selected, in the Edit menu, click Paste Special.


4. In the Paste Special dialog box, select Values, then click OK.

Note: You can paste the resulting values over the original data to “change” it, or you can select different destination cells to contain the plain text.

The steps above describe how to replace the formulas with corresponding values.

Removing extra spaces from cells with Trim in Excel

If you have data that contains additional spaces, there is a function you can use to trim them out.

1. Select a destination cell for your formula.


2. Type =trim([cell name]), then press Enter.

Note: According to Excel’s Insert Function dialog box, "Trim removes all spaces from a text string except for a single space between words."

Accessing all the functions in Excel with Insert Function

Excel contains a vast array of functions you can use to perform various calculations. You can use Insert Function to become familiar with the functions available in Excel and to become familiar with what each of the functions does.

Use Insert Function to insert a function you choose into an empty cell. You can search for a function by keyword or by category.

To launch the Insert Function dialog box

1. Select the empty cell where you want the function to be stored.


2. In the Insert menu, click Function.

The Insert Function dialog box appears.

Insert Function Dialog Box

3. In the Search for a function box, type a description of the function you want.

Note: Alternatively, you can use the category dropdown list to see functions that all relate to a certain type of calculation like “Lookup & Reference.”

4. In the Select a function area, click to select a function.

Note: Excel displays a description of the selected function below the Select a function area.

5. Click OK.


6. In the Function Arguments dialog box, click in an argument box.

Function Arguments

Note: Excel displays a description for the argument that corresponds to selected box.

If you have chosen a function that requires more than one argument, you can see descriptions for all required arguments by clicking in each of the boxes.

Launch of hemmans.com

When it's time, it's time.  This site has been waiting to be launched for several years.  And now, the time is right.