(C) Sheep - image by Krystyna Murphy 2020Over the many years that I have been training people in the use of Microsoft Excel, I have always enjoyed the subject of “Concatenation”. Firstly, because it instils dread in some and awe and others. Secondly, it just makes me giggle. Why? Because it is such a beautiful word and can create the most amazing results for people with just a little patience. It is not hard at all, just a little repetitive.

Microsoft has seen that this is such an important function that they have gone a little further and created a function all its own called “Flash Fill”, which I shall also talk about here.

What does concatenate mean?

Concatenate mean to join together or to combine.

In Excel it means you can combine text from two or more cells to make one cell with lots of words.

Let’s explore this together.

You probably have a data sheet with many columns of different data. You probably have separate columns for first name and surname in that data. But you need to have a column with both together. Concatenation is what you can use.

Here is a simple spreadsheet of names. List of names

In the third column, we shall put the two names together for each row.

  • Type in a column title in C1. Perhaps “Delegate”.
  • Select cell C2.
  • Locate and use the function Concatenate from the formula tab in Insert Function tool. Insert Function Tool

You should have this dialog box appear. Insert Function Dialog box


  • Select Concatenate if it is in the list in the lower section of this box, and then select OK button. Or type concatenate in the bot at the top ad select the Go button. Insert Function Dialog box
  • Click on the first line, and select the cell A2.
  • Click on the second line in this dialog box.
  • Type a space. This is most important, as if you do not, you will have the two names together with no separating space between them as a result.
  • A third line has automatically appeared for you to click in.

Select cell B2. filled in Function arguments

  • This is all we need for now, so select the OK button.

Voila! You are presented with this. Delegate Column

  • Autofill the cell C2 down the page and you have your two names in the same cell!

Completed list

Flash Fill

This is a relatively new feature that Microsoft brought out. Found on the Home Tab in the editing section. This is concatenation automated.

The same list as the one we started with will be used here.

  • Again type a new column title, such as Delegate.
  • Select the cell C2.
  • Type the name as you see it spelt in the cell A2 a space and then type the name as you see it in B2.

If you miss type then it will not work!

  • Press CRTL and Enter to accept the “formula” you have just typed in C2.
  • Select the Flash Fill tool.

Flash Fill Tool

Voila! You have your list of two names together just as with the Concatenation function but a lot quicker.

Create a sentence

If you desire to have a sentence with these names in the midst just type in what you require and do the same with the Flash Fill Tool. Have fun! But beware, there are a few tiny hiccups along the way, so always test your results to make sure you have what you need correctly!

Hiding and Unhiding in Excel

How to change case in Excel

Finding the cell that contains just a space in Excel



Please enter your comment!
Please enter your name here