Learn Excel Now

Conquer the Fear of Excel

  • Home
    • Our Team
  • Training
    • Custom Training
      • Custom Virtual Group Training
      • Custom Onsite Group Training
      • Consulting Services
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
      • Simply Excel Pt. II
  • Resources
    • Contact
    • FAQ
    • New User Onboarding
  • Login

Essential Excel Functions: How to Use CONCATENATE

July 6, 2016 by Tyrone Pernsley

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:

Excel CONCATENATE Image

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:

Excel Concatentate Gif 1

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

Excel Concatenate Image 2

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

Excel Concatenate Gif 2

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

Comments

  1. Dan Kruss says

    July 6, 2016 at 4:37 pm

    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

      July 13, 2016 at 10:50 am

      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.

  2. David Larson says

    July 6, 2016 at 4:40 pm

    What are the advantages and disadvantages of using Concatenate versus using the & symbol to combine data from two or more cell locations?

  3. Kipp Martin says

    July 6, 2016 at 4:42 pm

    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.

  4. maryanne.link says

    July 11, 2016 at 9:07 am

    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 @?

All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

  • Home
  • Training
  • All-Access
  • Excel Foundations
  • Resources

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-484-259-7664 or 1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2023 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In