How to join cells in Excel to create new numbers or sentences using CONCATENATE or &
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