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 use the CHAR function and an ASCII code 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.
The example may be easier to understand if you copy it to a blank worksheet.
Select the text in the example, starting with “First name” and ending with the phone number in the last row.
Press CTRL+C to copy the text.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
Note To see the result in the worksheet without breaking the lines of the name or address, expand the width of the columns.
The formula in the FUNCTION column of the preceding example table 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.
After you paste the example into your worksheet, drag the formula in D2 down to cells D3:D5 to combine the contents of those cells.
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 preceding example uses 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, CHAR(10) is used to enter line breaks in the cell.
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.