How to Concatenate a Range of Cells in Excel [5 Methods]

1. Using Concatenate + Transpose

The best way to combine text from different cells into one cell is using transpose function with concatenate function.

Look at the below range of cell where you have a text but its every word is in a different cell and you want to get is all in one cell.

concatenate a range of cells with concatenate transpose

Here are the steps:

  • In the B8, insert below formula and do not press enter.

=CONCATENATE(TRANSPOSE(A1:A5)&" ")

  • Now, just select the entire inside portion of concatenate function and press F9.
  • It will convert it into an array.
  • After that, remove the curly brackets from the start and the end of the array.
  • In the end, hit enter.

That’s all.

Explanation

2. Fill Justify to Combine Text

  1. First of all, make sure to increase the width of the column where you have text.
  2. After that, select all the cells.
  3. In the end, go to Home Tab ➜ Editing ➜ Fill ➜ Justify.

This will merge text from all the cells into the first cell of the selection.

3. With Text Join Function

If you are using Excel 2016 (Office 365), there is a function called “TextJoin”. It can make it easy for you to combine text from different cells into a single cell.

Syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

  1. delimiter a text string to use as a delimiter.
  2. ignore_empty true to ignore blank cell, false to not.
  3. text1 text to combine.
  4. [text2] text to combine optional.

How to use it

To combine below list of values you can use the formula:

=TEXTJOIN(" ",TRUE,A1:A5)

Here you have used space as a delimiter, TRUE to ignore blank cells and entire range in a single argument.

In the end, hit enter and you’ll get all the text in a single cell.

4. Combine Text with Power Query

Power Query is a fantastic tool and I love it. You can also use it to combine text from a list in a single cell. Below are the steps.

  • Select the range of cells and click on "From table" in data tab.
4 concatenate a range of cells with power query from table min
  • If will edit your data into Power Query editor.
  • Now from here, select the column and go to “Transform Tab”.
  • From “Transform” tab, go to Table and click on “Transpose”
  • Next, you need to merge all the columns.
  • For this, select all the columns (select first column, press and hold shift key, click on the last column) and press right click and then select “Merge”.
  • After that, from Merge window, select space as a separator and name the column.
  • In the end, click OK and click on “Close and Load”.

Now, you have a new worksheet your workbook with all the text in a single cell.

The best thing about using Power Query is you don’t need to do this setup again and again. When you update old list with a new value you need to refresh your query and it will add that new value in the cell.​

5. VBA code

Conclusion

​There may be the different situations for you where you need to concatenate a range of cell into a single cell. And, that’s why we have these different methods.

All methods are easy and quick, you need to select the right method as per your need. I must say that give a try to all the methods once.

Now Tell me one thing.

Which one is your favorite and worked for you?

Share your views with me in the comment section, I'd love to hear from you. And please don’t forget to share these methods with your friends.

2018-04-03T08:25:57+00:00
  • Pacifico T. Salangsang says:

    great!!! thank you sooo much..

  • Krish says:

    Justify method is awesome…

    • Puneet Gogia says:

      I’m so glad you liked it. 🙂

  • Carlos Mario Castaño says:

    I am working with Spanish version. When I follow your instructions, after F9 I get:

    =CONCATENAR({“Today “”is “”a “”beautiful “”day “})

    After removing curly brackets, In order to obtain the correct result, we must change “” by “&”. This was the only way it worked for me.
    Please correct me case I was wrong.
    Thanks. Very interesting article.
    Carlos.

    • Puneet Gogia says:

      You are right, / is not a concatenation operator.

  • Eric says:

    Hello Puneet,
    I like to use the macro. It works like a charm. Thank you.

  • Vitor Barreto says:

    This is great!

    I would (never) would have thought of these solutions (mainly the Concatenate one).

    Thanks.

    • Puneet Gogia says:

      I’m so glad to hear this.

  • Yashpal Moray says:

    Very nice guidance on concatenate function. I liked and found useful for me with the transpose function. Thanks a lot!

    • Puneet Gogia says:

      You’re welcome.

  • Jan Bolhuis says:

    Thanks for sharing

    • Puneet Gogia says:

      I’m so glad you liked it. 🙂

      • Lalla Prasad says:

        Very very useful information sir

        • Puneet says:

          I’m so glad you liked it. ?

  • Syed Waqar Hussain Shah says:

    fantastic knowledge

    • Puneet Gogia says:

      I’m so glad you liked it. 🙂

  • >