(1.) Select one option that you need to combine the cells based on under To combine selected cells according to following options, this example, I choose Combine columns In the popped out dialog box, please do the following operations: Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot:ģ. Select the range of cells that you want to combine based on a row.Ģ.
#EXCEL CONCATENATE FREE#
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.Īfter installing Kutools for Excel, please do as follows:ġ.
If there are multiple cells needed to be concatenated, the above formulas will be somewhat complex, here, I can introduce you a powerful tool- Kutools for Excel, with its Combine Rows, Columns or Cells without Losing Data utility, you can quickly concatenate multiple cells from a row or a column into one cell with any specific delimiters. If you want to use other separator after concertation, for instance, use comma to separate, you can change the formula to =CONCATENATE(A2,",",B2,",",C2). In the above formulas, A2, B2, C2 are the cells that you want to concatenate, if there are more cells needed to be combine, you just add the references after the formula, such as: =CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2).ģ. You can also apply this =A2&" "&B2&" "&C2 formula to combine the cells with space.Ģ. Then drag the fill handle down to the cells that you want to contain this formula, and the cells in the same row have been concatenated by space at once, see screenshot:ġ. Enter this formula =CONCATENATE(A2," ",B2," ",C2) into a blank cell besides your data, E2, for instance, see screenshot:Ģ. Here are some formulas may help you to solve this job. Supposing, I have the following three columns data, now, I need to concatenate the columns value with space separated to get the following result: In this case, how could you concatenate multiple cells into one cell and add spaces between the values in Excel?Ĭoncatenate multiple cell values and add space or other delimiters between words with formulasĬoncatenate multiple cell values and add space or other delimiters between words with Kutools for Excel But, sometimes, you want to add the spaces to separate the contents after concatenation. Normally, when you use the CONCATENATE function to combine several cell values, the contents will be joined together with no space. In this function, you can join up to 30 text strings together.How to concatenate cells and add space between words in Excel? The syntax of this function is as follows: You can also use the CONCATENATE() function to join multiple text strings together. ="You owe $"&D2&", which is due in 7 days" A slightly more advanced version of the formula above might look like the following:
#EXCEL CONCATENATE PLUS#
The & symbol acts like a +, or plus sign, to join text strings together into one. The secret here is to use the ampersand, &, to concatenate (or join) text and numbers together. To do this, type the following formula into A2:
#EXCEL CONCATENATE UPDATE#
As the value in D2 changes, you want the value in A2 to update automatically. Now, you want to have cell A2 display the text "Today's sales are $12,000", where $12,000 is the value calculated in D2. As the value in D2 changes, you want the value in A2 to update automatically.įor example, suppose you have calculated the total of a range of cells using a formula in cell D2.
Suppose you have calculated the total of a range of cells using a formula in cell D2. If you want to combine text with the results of a formula in a cell, you can use concatenation.