Excel help: How to split and combine columns

First in a series of Excel tips

One of the most common issues people struggle with in Excel is trying to determine how to split single columns into two (such as taking a single name column and dividing it to create first and last name columns), or how to combine multiple columns into one.

Neither is difficult.

Splitting columns into two:

  1. Click the letter in the header column of the column you intend to split, such as "A" for the first column.
  2. Select data from the menu bar.
  3. Select text to columns from the data menu.
  4. Select delimited in the new window and hit next.
  5. Select the proper delimiter (if there is a space between the first and last name, select space; if there is a comma, select comma; and so on) and then hit finish. See Figure 1.
In this case, comma is selected as the delimiter

Figure 1: Find the text to columns option under the data menu (top right). Comma has been chosen as the delimiter. When this is done, a preview displays how the columns will split.

Combining two columns into one:

  1. Put your cursor where you want the new column to appear (inserting a new column if necessary).
  2. Begin typing the formula like this: =concatenate(
  3. Then type the columns or information you want to create, such as:
    1. =concatenate(b2, a2,) This will combine the information in cells b2 and a2, in that order.
    2. To add a space, insert double quote marks with a space between them where you want the space to appear like this: =concatenate(a1," ", b1) as shown in Figure 2.
  4. Press return and you will see the result.
Type the formula and then press enter.

Figure 2: The key word for combining columns is concatenate. The formula displays until you press enter.

Split columns into two