Excel possess many formulas and functions to help you perform essential tasks. The CONCATENATE function is one of most useful. It allows you to combine data from two or more cells into a single string. There are many applications for this function. It is particularly useful when pulling an Excel spreadsheet from a database where pieces of information are broken up over different columns.
To get an idea of how to use this function, let’s take the example of phone numbers. In the following example, the area code, 3-digit prefix and 4-digit ending are all broken up in different columns:
Now, if we want to combine these into a single cell to make the phone numbers easy to view, copy and paste, we will use CONCATENATE. Here is how the formula looks:
= CONCATENATE (Text1,Text2,Text2,…)
Notice you need to identify each individual cell or text making up each component of the function. Here is how it looks when combining the columns to yield the phone numbers:
You can now use the Fill Handle to copy the function all the way down yielding phone numbers in the remaining rows.
Let’s take a look at another example. One of our fans, Susan, brought an interesting query to us a few weeks ago. She was pulling a list of email addresses from her company’s database.
Now, Susan told us that the company’s email worked like this: Firstname.lastname@companydomain.com
As you can see in the image above, the dot between first name and last name and the @ symbol between last name and company domain are missing.
So, to help Susan use the CONCATENATE, we first need to prep the data. Here are the steps to take:
- Add a column between First and Last Name
- Add a period in the new column – use the Fill Handle to copy it all the way the down
- Add a column between Last Name and Company domain
- Add the @ symbol and use the Fill Handle to copy it all the way down
- In the first open cell in the top row, enter the CONCATENATE function
- Use the Fill Handle to copy it all the way down
As you can see in the above image, the function was used successfully to create the email addresses.
We here at Learn Excel Now hope you now feel comfortable using the Excel CONCATENATE function.
Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter
Kevin – Learn Excel Now
Dan Kruss says
Concatenate also accepts text. So it may help keep your worksheets cleaner to use the function:
=CONCATENATE(A2,”.”,B2,”@”,C2)
in the column to the right of the domain.
Likewise, the domain column contains static data, so unless its is feeds into something else, you don’t really even need column C.
Kevin Erdman says
Hello and thank you for your comment. You are right that the text could have been added right into the CONCATENATE formula, but we also wanted to demonstrate how to add columns. It was for a basic introduction to the concept.
David Larson says
What are the advantages and disadvantages of using Concatenate versus using the & symbol to combine data from two or more cell locations?
Kipp Martin says
In my opinion, a much easier way of doing the email would be to add the period and the @ symbol in the concatenate formula. For example, the first line would be =CONCATENATE(A2,”.”,B2,”@”,C2). That way, you don’t have to add columns and copy a bunch of stuff around. I’ll understand if you think this is an advanced topic and you are trying to get the basic use of the formula across.
maryanne.link says
Just curious why you didn’t take this one step further and use the formula
=CONCATENATE(A3,”.”,B3,”@”,C3)
instead on inserting the two extra columns with essentially useless data, the period and the @?