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!

Contact Us

What is Remote Training?

Valued by Individuals

4.8/5
Over 25129 Reviews
Some very helpful tips, definitely things I can use in the future -Word Advanced Brisbane
Millie - Word Advanced, .
Very informative and engaging. Good teacher -Excel Intermediate Brisbane
Alisha - Excel Intermediate, .
was informative and well done -Excel Intermediate Brisbane
Sola - Excel Intermediate, .
thank you great course and facilitator was great -Excel Intermediate Brisbane
Luke - Excel Intermediate, .
very useful. -Excel Intermediate Brisbane
Jennifer - Excel Intermediate, .
Very clear and helpful. Good pace also. Well structured. -Excel Intermediate Brisbane
Aria - Excel Intermediate, .
This course was great. I knew very little about VBA prior to attending and Michael was clear and built knowledge so well that I feel confident attempting to apply this in real work situations. A highly recommended course. -Excel Macros/VBA 2 Day Parramatta
Mary - Excel Macros/VBA 2 Day, .
This course was amazing. Michael was such an engaging teacher and kept the class so informative and so easy to follow. We had a great time learning the content of this course and we loved the small class we were in. We could ask a lot of questions and enjoy the whole course at the same time. This was one of many courses I have attended with Nexacu and all the teachers are great. Michael is my favourite. 5 Stars from me from all perspectives !!! -Excel Macros/VBA 2 Day Parramatta
Jacqueline - Excel Macros/VBA 2 Day, .
Moved a bit quicker than the beginner but showed good examples of what you can do. I certainly need to go away and practice with some real data to apply what I have learned today. Many thanks -Power BI Intermediate Adelaide
Merrin - Power BI Intermediate, .
Thanks for the help! -Power BI Intermediate Adelaide
Bradley - Power BI Intermediate, .
Brilliant - learnt so much. Many thanks Jagg -Power BI Intermediate Adelaide
John - Power BI Intermediate, .
was able to learn new formulas and functions that will help me in the future. -Power BI Intermediate Adelaide
Estelle - Power BI Intermediate, .
Learnt a lot on how to manipulate and clean up data. Learnt to better manage permissions to data. Learnt more on the date functions, though still don't know how to calculate current week/previous week comparisons. -Power BI Intermediate Melbourne
Kristina - Power BI Intermediate, .
Learnt many items with Power Query and DAX, along with row level security which is of particular interest with the types of personnel reports I will need to build. -Power BI Intermediate Melbourne
Kylie - Power BI Intermediate, .
Excellent content, great teacher. Thank you! -Excel Intermediate Melbourne
Linda - Excel Intermediate, .
Steven was incredibly helpful in training us. He took the time to understand our professional roles and relate what we were learning to our day to day tasks. He was patient and professional. Thank you! -Word Intermediate Brisbane
Alison - Word Intermediate, .
I have enjoyed it and learned many tips. -Word Intermediate Brisbane
Kristian - Word Intermediate, .
This was great! -Word Intermediate Brisbane
Courteney - Word Intermediate, .
Well-paced and very informative training. Trainer is very knowledgeable and helpful with any questions. -Power BI Intermediate Brisbane
Emily - Power BI Intermediate, .
Great course. Jason was very clear and easy to understand. Learnt a lot in one day. Thanks! -Power BI Intermediate Brisbane
Ben - Power BI Intermediate, .

Trusted by Business

Procured by Government

Awards and Accreditations

Map of Australia

Follow us