Excel Function Concatenate or CONCAT to Make a Key for Colleague

Owned by Allison Urbanczyk
Last updated: Jan 13, 2021
3 min read

The Concatenate function, or CONCAT in more recent versions of Excel, is used to join two or more text strings into one string.

The steps in the first example below would help an academic advisor to use two columns from a report about advisees to join the student ID number and the academic program to create a column with the Key needed in Colleague to run a process such as BEVL: Batch Academic Evaluation and generate one PDF with all advisees' degree audits.

The steps in the second example below explain how to take 2 columns, such as first and last name columns, and combine them into a full name column.

Step-by-step guide

Example: Combining Student ID and Program Columns into a Colleague Key

  • In Microsoft Excel, select a column for entering the new information.
  • Click on an empty cell at the top of the column.
  • Type the following formula:
    • =CONCATENATE(A2,"*",B2)
    • If you are combining contents from other columns instead, use their labels.
    • Excel help text appears as you type to remind you of the next step in the formula.
    • A comma separates each component of the formula.
    • In this example, typing "*" between A2 and B2 inserts an asterisk between the ID and the program. This is important in Colleague keys. The key won't work without the asterisk.
  • Press enter, and the formula will be replaced with the results as shown below.
  • Click on the cell and "drag" the formula from the top cell down to the bottom of your list. Hint: double-clicking on the cell's bottom-right corner will quickly populate the whole column and stop where the text in the adjacent column stops.




Example: Combining First and Last Name Columns into a Full Name Column (LastName, FirstName)

  • In Microsoft Excel, select a column for entering the new information.
  • Click on an empty cell at the top of the column.
  • Type the following formula:
    • =CONCATENATE(B2,", ",A2)
    • By entering B2 before A2, you can choose LastName, FirstName formatting for your column. If you prefer FirstName LastName instead, type "=CONCATENATE(A2," ",B2). Entering " " adds the space between the names.
    • If you are combining contents from other columns instead, use their labels.
    • Excel help text appears as you type to remind you of the next step in the formula.
    • A comma separates each component of the formula.
    • In this example, typing ", " between B2 and A2 inserts a comma and space between the last name and first name.
  • Press enter, and the formula will be replaced with the results as shown below.
  • Click on the cell and "drag" the formula from the top cell down to the bottom of your list. Hint: double-clicking on the cell's bottom-right corner will quickly populate the whole column and stop where the text in the adjacent column stops.




For additional assistance, contact the IT Help Desk.