How to CONCATENATE a RANGE of Cells [Combine] in Excel

Top 5 Ways to Combine Values from a Range of Cells

Below are those ways which you can use to combine values from a range of cells.

  1. Using a Formula
  2. Fill Justify Option
  3. TEXTJOIN Function
  4. Power Query
  5. VBA Code

...so let's get started.

1. Using a Formula [CONCATENATE + TRANSPOSE] to Combine Values

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 cells...

...where you have a text but its every word is in a different cell and you want to get is all in one cell.

Below are the steps you need to follow to combine values from this range of cells into one cell.

  • In the B8, insert formula (=CONCATENATE(TRANSPOSE(A1:A5)&" ")) and do not press enter.
  • 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.
concatenate a range of cells with concatenate transpose

That’s all.

...how this formula works

In this formula, you have used TRANSPOSE and a space in the CONCATENATE.

...and when you hit enter, it combines all of them.

top excel tips tricks which can make you a pro this year

2. Combine Text using Fill Justify Option

And, whenever you need to combine text from different cells you can use it. The best thing is, you need a single click to merge 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. TEXTJOIN Function for CONCATENATE Values

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

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 to Combine Values

With this code, you can combine text in no time.

All you need to do, select the range of cells where you have text and run this code.

Sub combineText()

Dim rng As Range

Dim i As String

For Each rng In Selection

i = i & rng & " "

Next rng

Range("B1").Value = Trim(i)

End Sub

In the End,

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 and tell me:

Which one is your favorite and worked for you?

Please share your views with me in the comment section. I'd love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it.

You must Read these Next

  1. Convert a Formula to Value in Excel: Sometimes we need to convert all the formula in static values...
  2. Add Leading Zeros in Excel: Normally in a cell it's not possible to add a leading zeros but you...
  3. Count Cells with Text: For counting cells where you have text you can use these 6 different methods...
  4. Count Words in Excel: When it comes to Excel there is no such option to count words. You can...
  5. Count Unique Values in Excel: There are total six methods which you can use to count unique...
  6. Highlight Blank Cells in Excel: It's better to highlight blank cells and the best way to do this is by...
  7. R1C1 Reference Style: There few Excel users who know that there are two different reference style in Excel...
  8. Wildcard Characters in Excel: These wildcard characters are all about searching/looking up for a text...
  9. Hide Formula in Excel: Sometimes you simply don’t want to show a formula to others. In Excel, there..
  10. Remove First Character: To remove first or n characters from a string you can use these 5 methods and the...

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

43 thoughts

Leave a Comment

Your email address will not be published.

  1. Hello Sir,

    first of all would like to say thankful to you because few days ago I have been working on your blogs all are most important and helpful for us again thank you so much sir

    Regards
    Sandeep Singh

  2. Hi Puneet, when I try to combine cells using formula Concatenate with separater “; “ it returns “\” instead of “,” after I hit F9. Does it have something to do with Excel settings? I tried it at my collegue’s PC and it works as it should and we both use Excel 2010

  3. There were very useful tips. Thanks a lot. TEXTJOIN was something new I learned today. It was really handy.

  4. I tried 3 of these with partial success.

    * When I do the CONCATENATE(TRANSPOSE) one and hit F9 to create the array, it puts a semicolon between each character instead of a comma. Then I have to change all of them to commas for it to work correctly.
    * JUSTIFY just did nothing at all.
    * And when I tried TEXTJOIN it seemed to confuse my computer. It doesn’t seem to know what TEXTJOIN means.

    Any suggestions? (I’m using Excel 2010)

    • With justify, you need to make the cells (where you text) enough wide so that the entire text can be merged into one cell.

      • Ahhh, the text has to be arranged in a column for it to work! That appears to be the fastest option, but can it be done with Justify on text across a row?

    • One possible answer to the first problem – of a semi-colon instead of a comma – is explained in this article (easier than writing it out here ;-} ) .. https://www.excel-exercise.com/comma-or-semicolon-in-excel-formula/

      As you’re using Excel 2010, TEXTJOIN will not exist – you need Excel 2016 (365 version specifically, the standalone Excel 2016 doesn’t have it either [which annoys me!]). I’m not sure when JUSTIFY was added as an Excel formula. I think you need to upgrade your Excel.

  5. Dear Puneet,

    Thanks so much for this. These are very useful time and labor saving methods !
    Keep up the good work Sir !

  6. Good Tutorial. First two methods are good. Still, the 2nd option is the best. I am using Excel 2007.

    • In cell A1 there is 5.20
      Cell B1 there is 5.26
      and In Cell C1 there is 5.30
      when I combine these three cells using formula Concatenate with separater “-” It will shows result “5.2-5.26-5.3” instead of 5.20-5.26-5.30. Can you please guide how do I do?

  7. 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.

  8. This is great!

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

    Thanks.

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