• Home
• LTS Help Desk
• Workshops
• Permission to Use

Microsoft Excel 2003/2004

Functions: Concatenate

The Concatenate function creates a text string by pulling data from specified fields. This function can join information such as first and last names, or names and scores, which are in separate fields. Up to 30 fields may be added together in this fashion.

To create multiple text strings using identical sets of fields, you can select a range of cells in the Text text boxes, as long as the cell you selected for the results of the function is a row which includes the selected cells. The function will show only the text string for that row, but you can create text strings for the rest of the selected fields by copying the formula and pasting it into the Formula bar for cells in the corresponding rows for the rest of the information.

1. Open the desired Excel worksheet

2. Select the cell where you would like the result of the function to appear
   This should be in the same row as the information you will use to create a text string from.

3. From the Insert menu, select Function...
   OR
   Windows: From the Formula bar, click INSERT FUNCTION
   The Insert Function dialog box appears.
   Macintosh: From the Standard toolbar, click PASTE FUNCTION
   The Paste Function dialog box appears.

4. Windows: From the Or select a category pull-down list, select All or Text
   Macintosh: From the Function category scroll box, select All or Text

5. Windows: From the Select a function scroll box, select CONCATENATE
   Macintosh: From the Function name scroll box, select CONCATENATE

6. Click OK
   The Function Arguments dialog box appears.
   

7. In the Text 1 text box, type the range of cells
   OR
   1. Click COLLAPSE DIALOGor
   2. On your worksheet, select the cells to include

   3. Click RESTORE DIALOGor

8. To add supplementary text between fields, in a separate text box, type the text in quotation marks.
   To add a space between fields, in a separate text box, type " ".

9. Repeat steps 7 and 8 for as many of the Text text boxes as necessary
   NOTES:
   When you click in the last text box available, another will appear, up to a maximum of 30.
   If you are using more text boxes than can be displayed, use the scroll bar on the right to view them.

10. Click OK
    The text string appears in the selected cell and the formula appears in the Formula bar.
    EXAMPLE: The formula should look something like =CONCATENATE(B2," ",A2) with B2 being Text 1, a space being Text 2, and A2 being Text 3.