Combine Cells of Data in Excel
Excel's CONCATENATE function can be used to combine two or more cells of data together.
For example, if first and last names in a database have been separated into two cells the CONCATENATE function can be used to combine both names into a single cell.
The CONCATENATE Function's Syntax and Arguments
The syntax for the CONCATENATE function is:
= CONCATENATE ( text1, text2, ... text255 )
Text1, text2, ... text255 can be words, cell references, blank spaces, or numbers.
Up to 255 text entries can be added to the CONCATENATE function. Each entry must be separated by a comma.
Excel's CONCATENATE Function Example
As seen in the image above, this example will combine the data found in separate cells in columns D and E of a worksheet into a single cell in column F.
Note: The concatenate function does not automatically leave a blank space between words or other data. To add a space between the concatenated data we will add a space to line Text 2 of the dialog box using the space bar on the keyboard.
Entering the Tutorial Data
To follow this example,
- Enter the following data into specified cells as shown in the image above:
D1 - Staff Record
D2 - Joe
D3 - 555 -
D4 - 567
E2 - Smith
E3 - 1212
E4 - Main St.
Entering the CONCATENATE Function
- Click on cell F2 - the location where the results of the function will be displayed
- Click on the Formulas tab
- Choose Text Functions from the ribbon to open the drop down list
- Click on CONCATENATE in the list to bring up the function's dialog box.
- Click on line Text 1 in the dialog box
- Click on cell D2 in the worksheet to enter that cell reference into the dialog box
- Click on line Text 2 in the dialog box
- Press the space bar on the keyboard to add a space to line Text 2 (Excel will add double quotation marks around the space when we click on the next line)
- Click on line Text 3 in the dialog box
- Click on cell E2 in the worksheet to enter that cell reference into the dialog box
- Click OK to close the dialog box and return to the worksheet
- The concatenated text Joe Smith should appear in cell F2
- Repeat steps 2 to 12 above to add the function to cells F3 and F4 or use the fill handle to copy the function from F2 to F3 and F4
- When you click on cell F2 the complete function = CONCATENATE(D2, " " ,E2) appears in the formula bar above the worksheet