How to Join Text Together in Excel Using CONCATENATE and &

Often data imported into Excel from another system isn’t quite in the format we want. Text can be split across columns, for example; the telephone area code below is in column A and the remainder of the number in column B.

Here you can use the CONCATENATE formula to merge cells together into one cell, or you can simply use the ampersand ‘&’ symbol to join your text.

How to use CONCATENATE to Join Text

In Excel the CONCATENATE syntax is:

=CONCATENATE(text1, [text2],….)

Using our example our formula will be:

=CONCATENATE(A2,B2)

And the result is:

0751547318

We can make it a bit more elegant with the addition of some symbols and spaces to separate the area code from the number like this:

=CONCATENATE(“(“,A2,“) “,B2)

With this result:

(07) 51547318

Notice that the brackets are in quotes and I’ve added a space after the second bracket. This is because when adding characters, other text, or spaces you need to surround them in quotes.

How to Use the Ampersand to Join Text

A simpler formula uses the ampersand, ‘&’, instead of CONCATENATE.  Using the above example our formula will be:

=(“(“&A2&“) “&B2)

This gives us the same result, but you’ll notice that the commas are replaced with the ampersand.

I like using the ampersand for joining just a few cells together, but if you’ve got lots of cells to join or you want to add lots of additional text, symbols or spaces then CONCATENATE is a bit quicker. Especially with Excel 2007 and its predictive formulas where you only have to type out ‘=CON’ and then tab to select the first formula in the list. The image below shows you what predictive formulas look like if you don’t have Excel 2007 or higher.

Insert Additional Text

You can also insert additional text like this:

=“Phone “&A2&” “&B2

With this result:

Phone 07 51547318

You can join as many cells together as you like, with the limit of 255 characters in a single cell.

What if you want to format the phone number with a  space like this:

(07) 5154 7318

You could join the text together using CONCATENATE or the ampersand, then paste the result as a value and apply a custom format, or you can use the LEFT and RIGHT formulas like this:

=“(“&A2&“) “&LEFT(B2,4)&” “&RIGHT(B2,4)

With this result:

(07) 5154 7318

Download the workbook here to practice and reverse engineer the formulas.