Combine Values from Multiple Cells into One Cell in Excel

Add to Favorites

There are two easy ways to combine values from multiple cells in Excel.

In order to do this, we need to do what is called "concatenate" values.

Method 1 - CONCATENATE Function

  1. Type =CONCATENATE( into the cell where you want the combined text to appear:
  2. Select the first cell that you want to combine:
  3. Type a comma and then select the next cell that you want to combine:
  4. Repeat step 3 until you have selected all of the cells:
  5. Type the closing parenthesis for the function and hit Enter and that's it!

Now all of the text is combined but it looks rather odd because there are no spaces between the text.

Add Spaces Between Combined Text

We follow the same steps as above except that, between each cell that we want to combine, we type this " " which is just a blank space.  It looks like this:

Which ends up looking like this:

Method 2 - Ampersand

You can combine cell values without having to use a function at all.

You simply use the ampersand character &

  1. Go to the cell where you want the combined text to appear and type an equals sign and select the first cell to combine:
  2. Now type an ampersand and select the next cell:
  3. Repeat this until all cells have been selected:
  4. Hit enter when you are done (there is no need for parenthesis here):

Once again, there are no spaces between the text.

Add Spaces Between the Combined Text

We do it just like before in that we need to add spaces between the text using a space between two double quotation marks (you could also just add a space after the text in each cell that you want to combine).

Here is the result:

Notes

This saves you a lot of time when you have a large set of data where you need a simple function to combine the data in each row. Using the methods above, you just create the formula or function once and copy it down the entire column of data.

Don't forget to download the accompanying spreadsheet so you can see this feature in action.


Excel Function: CONCATENATE()
Downloadable Files: Excel File

Similar Content on TeachExcel
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
Tutorial: How to combine a list of data into one cell while putting a delimiter between each piece ...
Combine Multiple Workbooks into One
Macro: This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets...
Combine Worksheets from Multiple Workbooks into One
: Excel macro that allows you to select multiple workbooks and have all of their worksheets ...
Tutorial Details:
Excel Function: CONCATENATE()
Downloadable Files: Excel File
Similar Content
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Excel Forum