Copyright

How to Use the CONCATENATE Function in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Excel Charts & Graphs: Summarizing Data Visually

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 To Concatenate
  • 0:52 Using Concatenate in Formulas
  • 2:14 Example Worksheet
  • 3:37 Example Formulas
  • 6:12 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Login or Sign up

Timeline
Autoplay
Autoplay

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Ever want to combine two columns into one? This lesson will demonstrate how to use the Concatenate function in Excel. Using this function in a formula will allow you to merge multiple columns of data into one.

To Concatenate

My colleagues, and even at times my family members, will reach out to me for help because they know that I am an avid Excel user. It's interesting how often the question comes up, how do you merge columns? Or sometimes they might ask, what is that word or function you use in a formula to merge columns?

Well, the word or function is CONCATENATE. The term is Latin, originating from the word concatenatus. Simply put, Concatenate means to link together.

In this lesson, you will learn how to use the Concatenate function in a formula. We will also look at some example formulas and common situations where you would want to use the function. This function helps you to save time and to work more efficiently.

Using Concatenate in Formulas

Before we venture off and look at a worksheet using Concatenate, let's first look at the formula. More specifically, the formula syntax or structure and arrangement of the formula components. Here is the syntax.

=CONCATENATE(text1,text2,. . .)

For example, first and last names displayed in two, separate columns. The first name is displayed in column A, while the last name is displayed in column B. We would like to combine or join together both names in column C. In this instance, the formula for column C would be =CONCATENATE(A2,B2).

Now that you have the basics, let's move on and look at an actual Excel worksheet. We will also take a look at additional components or arguments you can include in a formula to add punctuation, such as periods, commas, spaces or even other characters.

Example Worksheet

Probably the most common usage for the CONCATENATE function is to merge first and last names, displayed in two different columns. Another common way you might use the function is to combine address information, such as street, city and state. Some more advanced users of Excel create worksheets that can be imported into a database. Let me give you an example.

Let's assume that we have a worksheet with the first name, middle initial and last name appropriately displayed in columns A, B and C. We need to combine all three pieces of information in order to create an email address. The worksheet will then be imported into an email system to generate email addresses for the company.

Sample Excel spreadsheet

Before writing the formula in column D, a couple of things. You will need a period between each part of the name. You will also need the at sign (@) displayed after the name, including the company name, followed by .com. Got it? For instance, karen.e.sorensen@myawesomecompany.com.

In the worksheet example, we'll start with the data in row 3. Here is the formula for cell D3.

=CONCATENATE(A3,''.'',B3,''.'',C3,''@companyname.com'')

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create An Account
Support