Excel has a built-in formula for concatenation. It looks like this:
=concatenate(A2, B2) or = concatenate(A2,””, B2)
This works, but I am blogging about shortcuts! There is another way to perform the concatenate function. You can also use the “&” symbol:
=A2&B2 or =A2&””&B2
Imagine you have a spreadsheet with a column for First Name and a Column for Last Name. You want a column with the entire name in it, like “Smith, John”. The thing to remember with concatenation is that you have to include everything in the formula, even the spaces and commas. If there is any text other than what is in a selected cell, it should be surrounded by quotations.
In order to combine First Name “John” from column A and Last Name Smith from Column B in the format mentioned above, you would write the formula as follows.
=B2&” , “&A2
This would give the result Smith, John.
Note: You can combine as many cells as you like in a single concatenation formula. Typically, starting with the first cell and copying your formula down the column is the preferred method.
If you are an Advocate Capital customer and need help preparing or manipulating data in the Excel application, feel free to contact me at [email protected].
We love feedback from our customers and implement suggestions often. What shortcut do you use the most? If you have a question or idea for an Excel tip that you would like to see, let me know and I might write about it!
Training Specialist
Photo Credit: gstockstudio