Essential Excel Functions: How to Use CONCATENATE

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