MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


If you want to join the contents of two or more cells then you have two ways to this in Microsoft Excel. Excel has Function to do this.

The Concatenate Function

To join or concatenate two or more cells you list the cells in the Function like this

=CONCATENATE((A1,B1)

This will combine the two cells the two cells, so if A1 contains John and BI Smith the formula result will be John Smith. The same result will be returned if there is a blank cell between the two text items by using:

=CONCATENATE(A1,C1)

However, if you want to change this to last name followed by first name then using Concatenate reverse the list of cells.

=CONCATENATE((B1,A1)

To make the result more acceptable it should be Smith, John. To do this with Concatenate requires an extra cell containing the comma. So you would have John in A1, a comma in B1 and Smith in C1.  The formula would then be

=CONCATENATE((A1,B1,C1)

Having a column in a database simply to contain such characters isn’t a good option. Fortuinately, Excel has a different method of joining cells. You can simply use & instead of Concatenate.

 =CONCATENATE((A1,B1)  would become  =A1 & B1

=CONCATENATE(B1,A1) would be =B1 & A1

Now using & you don’t need an extra cell to add the comma, you can incorporate characters, etc. into the formula. Note the space after the comma.

=B1 & “, ” & A1



Joining cell contents…