Combine two or more columns by using a function

Suppose you have two or more columns of data that you want to combine in a single column, such as the name and phone number of a person. To combine two or more columns, use the CONCATENATE function in a formula in a nearby cell (typically to the right of the last column of data that you want to combine), and then drag that formula down through the rows that contain the data. When you create your formula, you can add a space or comma to cleanly separate names and addresses in the new column by enclosing them in quotation marks (" "). You can also specify an ASCII code in the CHAR function to insert a special character, such as a line break, when you're combining the data so that names are on a separate line from street addresses and city, state, and postal codes.

Example 1

The formula in the following example uses the CONCATENATE function to combine the contents of three cells in columns A, B, and C. In the function, you separate the strings that you want combined by commas. To add a space between the strings, include a space enclosed in quotation marks (" "). If you look closely at the CONCATENATE function in the formula, you’ll see that the contents of A2 are combined with a space, the contents of B2, another space, and the contents of C2.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Example 2

Here’s a similar example that uses the CHAR function to insert a new line. These results are better suited for a mailing label.

The examples use the CHAR function inside the CONCATENATE function to insert a character that can be difficult to type in a formula (such as a comma or a space character, because either one might make the formula hard to read), or some other non-alphanumeric character, such as a line break or symbol. In the example, the CHAR function is used to enter line breaks in the cell. The entire formula is shown in cell A5, and the actual result is in cell F2.

The CONCATENATE function combines "The " with the Last Name in A2, " Family," a line break, which is CHAR(10), the Address in B2, another line break, the City in C2, a space (" "), the State in D2, a space, and the ZIP Code in E2.

The CHAR function requires a number value between a pair of parentheses. The function passes this number to Excel, which returns the character that corresponds to that number. The line break character is represented by the number 10, so to create a line break use the CHAR function in the formula like this: CHAR(10).