MAKING EXCEL WORK FOR YOU
© Roy Cox 2005 to 2013
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
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:
However, if you want to change this to last name followed by first name then using Concatenate reverse the list of cells.
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
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
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|