Menu
Cart 0

How to join the contents of multiple cells in Excel

Posted by Roger Hyttinen on

Excel allows you to quickly combine (or concatenate) the contents of two or more cells into a new cell. There are two different methods for doing this: using an Ampersand or using the Concatenate function.

Method 1: Combine Cells Using the Ampersand

To combine the contents of two or more cells, separate each cell in the formula with an ampersand (&). For instance, if cell A1 contained the text John and cell B1 contained the text Smith, you can join them with the following formula:

=A1&B1

However, the result will be: JohnSmith

This is perhaps not quite what we want. The combining of the two cells neglected to add an intervening space between the names. To add a space between the two words, add the space surrounded by quotations, followed by an ampersand:

=A1 & ” ” & B1

This will return: John Smith.

Let’s say you want to combine two cells, one containing the city (Chicago) and one containing a state (IL). In this case, you’ll want to add a comma and a space to the formula, to obtain the result: Chicago, IL. The resulting formula would be:

=A1 & “, ” & B1

Once you’ve entered in the formula, use the fill handle to drag the formula into the rows below, for as many entries as there are.

Method 2: Combine Cells using the Concatenate Function

If you don’t want to mess around with ampersands, there’s another way to join the contents of cells and that’s using the Concatenate function. Instead of an ampersand separating the different entries, use a comma instead. So using the example above, to combine cells A1 and B1, you would use the following formula:

=CONCATENATE(A1, ” “, B1)


Using our city and state example, complete with a comma separator, the formula would read:

=CONCATENATE(A1, “, “, B1)

Use whichever of these two methods you prefer - they both work equally well.

 


Share this post



← Older Post Newer Post →


Leave a comment

Please note, comments must be approved before they are published.