How to join cells in Excel to create new numbers or sentences using CONCATENATE or &.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Join cells in Excel
CONCATENATE allows you to join cells in Excel e.g. segments of text or numbers to form new text or numbers, and is especially useful when handling reports.
In this segment you will learn how to find, activate, and use the function; and how to use an alternate shortcut.
In this example we have two reports. Report 1 seems to split up the Division, Client Number and the Branch, between 3 different columns. Where as Report 2, appears to add them all together.
What we need for comparison purposes to make it easier to compare these 2 reports, is a column that combines all these columns.
We can use the CONCATENATE function to achieve this,
click in the cell where you want the formula to appear
Click on the Function Wizard
Find the Text segments
And click on CONCATENATE
What it’s asking for is now the different segments that are going to make up this new cell, as you can see here, we need the Division first,
so we’ll click in Division
Text 2, we seem to require a dash, so we can type a dash in there
3, appears to be the Client Number, so we can refer to that cell there
again we require another dash, at Text 4 is a dash
and Text 5 appears to be the Branch Number, so we can click on branch
and when we say ok we get an exact duplicate
and if we copy
and paste this down, it is now significantly easier to compare these two reports
There is another way to achieve this same result,
you can delete this,
you can also do as follows type equals (=)
go the first one,
and then you put the & sign like that
and we need a dash so we put inverted commas dash
and then we do the & again
click on Client Number
inverted commas dash
and Branch Number
and when we push enter we get the exact same cell
which we can now copy down