Combining data from different columns in Excel can make your spreadsheets easier to read and more organized. One of the quickest ways to achieve this is by using Excel’s CONCAT function (previously known as CONCATENATE). Today, I’ll walk you through how to concatenate or combine data in Excel, saving you time and effort.

In this tutorial, we’ll use real-world examples to join employee-related data, and you’ll learn how to control what appears in each cell by adjusting your formulas. Let’s dive in!

What Does Concatenate Mean in Excel?

Concatenate means to combine or link different pieces of information into one. In Excel terms, it involves merging data from multiple cells, whether it’s texts, numbers, or a combination of both. This is especially useful if you want to combine names, labels, IDs, or any other data that are in separate columns but need to be shown in a single column for easier viewing or reporting.

Step-by-Step: How to Use the CONCAT Function to Combine in Excel

We’ll start by learning the basic way to concatenate columns in Excel. Here’s how to combine employee first and last names into one column.

Example 1: Concatenate or Combine First and Last Name Data in Excel

We have the following dataset:

  • Column A: Employee ID
  • Column B: Type (Full-time/Part-time)
  • Column C: First Name
  • Column D: Last Name

We’ll concatenate or combine the First Name and Last Name data into a new column in Excel, showing a space between them.

Steps:

  1. Click into the cell where you want the combined data to appear (e.g., E2).
  2. Go to the Formula bar, click Insert Function, and type CONCAT.
  3. Select the CONCAT function from the list. This will open up the Function Arguments box.
  4. In Text 1, select the first cell with data you want to combine (C2: First Name).
  5. In Text 2, type “ ” (this inserts a space between the names).
  6. In Text 3, select the next data you want to combine (D2: Last Name).
  7. Press OK.

RELATED TOPICS: How to Use the IF Function in Excel

You should now see that First Name and Last Name data are concatenated or combined in one cell in Excel—for example, Heidi O'Brien. To apply this to all rows, double-click the fill handle at the bottom right of the cell.

Add a Space Between Concatenated or Combined Data in Excel

By default, the CONCAT function does not add spaces between the combined values. If you need them, you’ll have to manually insert a space in quotes (" "). For example, if concatenating or combining first and last name data in Excel without it would result in HeidiO’Brien, but by inserting a space, you will get Heidi O’Brien.

Example 2: Create a Custom Employee ID

Let’s step it up. Suppose we want to create an Employee ID that combines the following:

  • Employee Number (Column A)
  • Type (Full-Time or Part-Time, Column B)
  • The first three characters of the First Name
  • The first three characters of the Last Name

Go ahead, and follow these steps:

  1. In cell F2, type the following formula manually: =CONCAT(A2, B2, LEFT(C2, 3), LEFT(D2, 3))
  2. Press Enter.

In our example, this will return an ID like this: 139FullHeiO'B. To fill in the entire column, double-click the bottom-right corner of the cell and it will auto-fill for all employees.

RELATED TOPICS: How to Use the VLOOKUP Function in Excel

Explanation of the Formula:

  • A2 grabs the Employee ID.
  • B2 grabs whether the employee is Full or Part-time.
  • LEFT(C2, 3) grabs the first 3 characters of the first name.
  • LEFT(D2, 3) grabs the first 3 characters of the last name.

Example 3: Use the Last Three Characters Instead. Now, what if you want to get the last three letters of both the first and last names, instead of the first three? Here’s how:

  1. In cell F2, modify your formula: =CONCAT(A2, B2, RIGHT(C2, 3), RIGHT(D2, 3))
  2. After pressing Enter, this will show something like: 139Fullidiien.

The RIGHT() function grabs data starting from the right side of the text. In this case, it pulls the last three letters from both the first and last names.

Adjusting for Different Scenarios

At times, you may want a mix of data, such as the first three letters of the first name and the last three of the last name. With a little adjustment to the formula, you can handle that too:

  • Formula for first three letters of the first name and last three of the last name: =CONCAT(A2, B2, LEFT(C2, 3), RIGHT(D2, 3)) This formula will give you something like 139FullHeiien.

Key Tips When Concatenating or Combining Data in Excel

  • Don’t forget spaces: Remember to add " " between cells if you need spaces between concatenated values.
  • Use LEFT and RIGHT to control how many characters you extract from text cells.
  • Manually enter formulas for more complex operations instead of relying on the Insert Function wizard.
  • Descriptions in complex functions: If the formula looks complicated, break it down into parts to understand what each piece is doing.

RELATED TOPICS: Excel Made Easy: Can You Spot Formulas vs. Functions?

Final Thoughts

Learning how to concatenate or combine data in Excel is a huge time-saver, whether you’re working on employee records, custom IDs, or merging any type of information. Once you get the hang of using the CONCAT function, you’ll find that organizing and managing data becomes far more efficient.

Don’t be afraid to experiment by combining text, numbers, or even pulling only sections of strings using LEFT and RIGHT functions. Practice makes perfect!

If this tutorial helped, make sure to give the video a thumbs up and consider subscribing to my channel for more Excel tips. You can also find the exact formula from the tutorial in the video description if you need to copy it directly.

Got any questions about using CONCAT or suggestions for future tutorials? Drop them in the comments below. Thanks for reading and I’ll see you in the next tutorial.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR