All courses available in-class or via remote login

Microsoft Excel list with names

Best Use - "Concatenate" Function

June 01, 2017

The Concatenate Function

Concatenate is one of the most effective ways of combining data in Microsoft Excel.

Fundamentally there are two ways in which you can combine data in Excel spreadsheets, you can:

  • Merge cells or,
  • Concatenate cell values

 

When you merge cells, you are “physically” merging two or more cells into a single cell. As a result, you have one larger cell that is displayed across multiple rows and/or columns in your worksheet.

 

When you concatenate cells in Excel, you combine only the contents of those cells. Strictly speaking, concatenation in Excel is the process of joining two or more values together. This method is often used to combine a few pieces of text that reside in different cells (technically, these are called text strings or simply strings) or insert a formula-calculated value in the middle of some text.

 

Below is a short tutorial on how to effectively use the concatenate function:

 Step 1:

Consider the following list that has both First & Last name columns. We will use concatenate function to combine both the names to create a "Full Name column".

Step 2:

excel exert

Select a cell (D2) where you want to put your Full Name. Then click the Insert Function command in Formula Tab and find Concatenate Function. Click OK.

Step 3: 

excel exert

The CONCATENATE dialog box pops up on the screen and the Formula Name appears in the cell. Notice that, this function has two arguments
 

Step 4: 

excel exert

Select first argument (Text1) and then click on First Name (Brian). Address of the cell (B2) appears in the box.

 

Step 5:

excel exert

Select the second argument (Text2) and notice that a third argument (Text3) automatically appears in the dialog box. Type “ “ in the box. By doing this, we instruct our function to put one space between the names.

 

Step 6: 

Now select 3rd argument and click on Last Name (Houson). Address of the cell (C2) pops up in the tab.



 

Step 7:



 
Click OK and you will see both names have been combined with a space in between.

 

Step 8:

excel exert

 
Point towards bottom right corner until you see a four-headed black arrow and double click. Your entire column is populated with Full Names!



So there you have it! A simple and effective way on how to use the concatenate function. Thanks for reading!

What is Remote Training?

Valued by Individuals

 

4.8/5
Over 21470 Reviews
Jagg was an excellent trainer - very patient, had lots of options for people who didn't have the newest versions. I feel really confident about what I learnt today and I am very happy I did this course! -Power BI Beginner Adelaide
Melissa - Power BI Beginner, .
Excellent! Very knowledgeable instructor. -Power BI Beginner Adelaide
Jason - Power BI Beginner, .
Great course, very informative. Great instructor, very helpful -Power BI Beginner Adelaide
Sarah - Power BI Beginner, .
Great session, moves at a good pace with support when needed. Thanks -Power BI Beginner Adelaide
Matthew - Power BI Beginner, .
Excellent! -Microsoft Applications - Customised Sydney
Candice - Microsoft Applications - Customised, .
Very helpful -Microsoft Applications - Customised Sydney
Janet - Microsoft Applications - Customised, .
The learning environment and facilities are great. Ken knew the course content and is a friendly trainer.. -SharePoint Site Owner Melbourne
Robbie - SharePoint Site Owner, .
Thank you Ken Great 2 day of learning lots of information, looking forward to making our New Company SharePoint Site -SharePoint Site Owner Melbourne
Natasha - SharePoint Site Owner, .
Thank you Ken, I feel a lot more empowered and up-skilled with regards to SharePoint. I am looking forward to supporting my organisation to leverage SharePoint as one of collaboration and information management tools. -SharePoint Site Owner Melbourne
Annita - SharePoint Site Owner, .
Found it beneficial. Ken was extremely knowledgeable -SharePoint Site Owner Melbourne
Toby - SharePoint Site Owner, .
Great course, learnt a lot for the trainer. -Power BI Beginner Sydney
Aaron - Power BI Beginner, .
A good session however some revision of content from the intermediate course, which meant doing those two courses close together made those portions somewhat repetitive. Jagg was a good presenter and a good pace. -Excel Advanced Adelaide
Belinda - Excel Advanced, .
Jagg went above and beyond to help us learn the advance functions of excel. -Excel Advanced Adelaide
Ateeq-ur - Excel Advanced, .
Jagg was a great instructor - he stepped us through at a good pace, paused well to take q's and was well prepared. -Excel Advanced Adelaide
Hannah - Excel Advanced, .
Excellent range of advanced skills presented -Excel Advanced Adelaide
Oliver - Excel Advanced, .
Jag was excellent! Clear and thorough. I was a bit worried doing it remotely but found it better! Highly recommend. -Excel Advanced Adelaide
Agland - Excel Advanced, .
Was a very good informative session from Jag. -Excel Advanced Adelaide
Nilmini - Excel Advanced, .
Thank you. Well taught, clearly presented, and valuable lessons. Would recommend. -Excel Advanced Adelaide
David - Excel Advanced, .
Good pace, great course -Excel Intermediate Melbourne
Gemma - Excel Intermediate, .
Was a very informative course. John is a very patient instructor. Gives clear instructions. -Excel Intermediate Melbourne
Sonia - Excel Intermediate, .

Trusted by Business

Procured by Government

Map of Australia