1. Computing

Concatenate Text Data in Excel

By

Combine Cells of Data in Excel
Combine Cells of Data in Excel

Combine Cells of Data in Excel

© Ted French

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.

Concatenate Function Syntax

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

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

  1. 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

  1. Click on cell F2 - the location where the results of the function will be displayed

  2. Click on the Formulas tab

  3. Choose Text Functions from the ribbon to open the drop down list

  4. Click on CONCATENATE in the list to bring up the function's dialog box.

  5. Click on line Text 1 in the dialog box

  6. Click on cell D2 in the worksheet to enter that cell reference into the dialog box

  7. Click on line Text 2 in the dialog box

  8. 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)

  9. Click on line Text 3 in the dialog box

  10. Click on cell E2 in the worksheet to enter that cell reference into the dialog box

  11. Click OK to close the dialog box and return to the worksheet

  12. The concatenated text Joe Smith should appear in cell F2

  13. 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

  14. When you click on cell F2 the complete function = CONCATENATE(D2, " " ,E2) appears in the formula bar above the worksheet

©2014 About.com. All rights reserved.