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

Table of Content Close
1. Using a Formula [CONCATENATE + TRANSPOSE] to Combine Values
How this formula works
2. Combine Text using Fill Justify Option
3. TEXTJOIN Function for CONCATENATE Values
Syntax:
how to use it
4. Combine Text with Power Query
5. VBA Code to Combine Values
In the End,

To combine values CONCATENATE is the best way, but with this function, itā€™s not possible to refer to an entire range. You need to select all the cells of a range one by one, and if you try to refer to an entire range, it will return the text from the first cell.

In this situation, you do need a method where you can refer to an entire range of cells to combine them in a single cell. So today in this post, Iā€™d like to share with you 5 different ways to combine text from a range into a single cell.

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

The best way to combine text from different cells into one cell is using the transpose function with concatenating function. Look at the below range of cells where you have a text but every word is in a different cell and you want to get it all in one cell. Below are the steps you need to follow to combine values from this range of cells into one cell.

  1. In the B8, insert formula (=CONCATENATE(TRANSPOSE(A1:A5)&" ")) and do not press enter.
  2. Now, just select the entire inside portion of concatenate function and press F9. It will convert it into an array.
  3. After that, remove the curly brackets from the start and the end of the array.
  4. 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 space in the CONCATENATE. When you convert that reference into hard values it returns an array. In this array, you have the text from each cell and a space between them and when you hit enter, it combines all of them.

2. Combine Text using Fill Justify Option

Fill justify is one of the unused but most powerful tools in Excel. 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. 

Have look at the below data and follow the steps.

  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 the 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 the 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. Make sure to check out this (Excel Power Query Tutorial). 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.
    5-concatenate-a-range-of-cells-with-power-query-editor-min
  • Now from here, select the column and go to ā€œTransform Tabā€.
  • From ā€œTransformā€ tab, go to Table and click on ā€œTransposeā€.
    6-concatenate-a-range-of-cells-with-power-query-transpose-min
  • 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ā€.
    8-concatenate-a-range-of-cells-with-power-query-merge-cells-min
  • After that, from Merge window, select space as a separator and name the column.
    9-concatenate-a-range-of-cells-with-power-query-new-column-name-min
  • In the end, click OK and click on ā€œClose and Loadā€.

Now, you have a new worksheet in 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 the 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

If you want to use a macro code to combine text from different cells then I have something for you. With this code, you can combine text in no time. All you need to do, select the range of cells where you have the 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

Make sure to specify your desired location in the code where you want to combine the text.

In the End,

There may be different situations for you where you need to concatenate a range of cells 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
  2. Add Leading Zeros in Excel
  3. Count Cells with Text
  4. Count Words in Excel
  5. Count Unique Values in Excel
  6. Highlight Blank Cells in Excel
  7. R1C1 Reference Style
  8. Wildcard Characters in Excel
  9. Hide Formula in Excel
  10. Remove First Character

 

54 thoughts

Leave a Comment

Your email address will not be published.

  1. All your solutions are excellent.
    Many different methods are also taught.
    Though I am very good at Excel, I learn many many things from your website.
    THANKS A LOT.
    God Bless.

    Reply

  5. Hello,
    Is it possible via formula, w/o vba nor Power Tools, to combine 2 arrays (generated by formula) into a non-array value separated by comas. Textjoin will work with only 1 array but returns the first element when there is a division of array inside the Textjoin formula. I also tried to have another cell to do the division w/o luck. If u need to know why I’m doing this, I’ll be glad to add more details.
    TQ, KR!

    Reply

  7. Thank you for this VBA code. It works great! However, I want to add one more aspect to the code and I can’t quite figure it out.

    I would like for the code to locate a cell, based on a certain text, then select everything in the list below that cell. Is that something easily doable?

    Reply

  8. Good morning.

    Thanks for the interesting article. Unfortunately, as far as I can see, none of the methods actually does what I want.

    I am writing an accounting spreadsheet, and I want a column that shows an error message if there is anything unexpected in the columns where I enter data, or in the calculation columns. At present, it is a huge long tangle of nested if statements and messages joined by “&” operators. I want to simplify this by setting up some columns, each of which detects a particular error, and generates an appropriate message if it finds it. This will make it much easier to add more error checking in future – just insert more columns in this part of the sheet.

    In this scenario, the error message column (the one that is always visible) needs to be a concatenation of all the individual error columns.

    The first two methods don’t work, since they use the values of the cells at the time you type the formula, while I want one that continually updates as the data – and messages – change.

    The third method doesn’t work because I am using the standalone Excel 2016, which doesn’t have TEXTJOIN. This is particularly frustrating, because TEXTJOIN is exactly the function I need!

    One day, I intend to add some VBA code to my spreadsheet to cover things like year end (when I have to chop off a year’s worth of data and replace it with a few lines of carried-forward values), but I have never programmed in VB (of any flavour), so this is going to be a major project. (Maybe I’ll use Python code instead.) The problem I foresee here is that code generally executes when the user clicks a button; formulae execute when you change a cell on which they depend. If I set up a VBA program to execute every time I enter data, and it computes this for every line of the spreadsheet, it introduces a huge processing overhead! And if I (or anyone else using the sheet) can remember to click a “check for errors” button, we can remember to check for errors anyway.

    So this leaves Power Query. I’d never even heard of this tool before I read your article! If (as I suspect) I need to click something to refresh my query in order to check for errors, then it suffers from the same problem as VBA. But it’s worth further investigation. Thank you for bringing it to my attention.

    Maybe I need to migrate to Excel 2019. Now that I have (I think) sorted out all the bugs that were introduced when I migrated from Excel 2007 to Excel 2016, that’s a possibility. (The bugs mostly concerned the changed behaviour of SUMIF when the data range and criterion range were different lengths)

    In the meantime, I shall probably set up the columns I need, and concatenate them with & or CONCATENATE. I’ll just have to remember to add more arguments to the formula whenever I add more error checking columns.

    Philip.

    Reply

    • Philip

      This may be a bit out of date now. However, if the above VBA is adjusted, it can be used to return the concatenated strings as a normal formula, enter (something like) the following into a VBA module (Alt-F11, Insert-Module):

      “Public Function RANGECAT(rng1 As Range, Optional rng2 As Range) As String
      Dim r1 As Integer, c1 As Integer, r2 As Integer, c2 As Integer
      Dim cel As String

      cel = “”
      For c1 = 1 To rng1.Columns.Count
      For r1 = 1 To rng1.Rows.Count
      cel = cel + rng1.Cells(r1, c1)
      Next r1
      Next c1
      If rng2 Is Nothing Then
      Else
      For c2 = 1 To rng2.Columns.Count
      For r2 = 1 To rng2.Rows.Count
      cel = cel + rng2.Cells(r2, c2)
      Next r2
      Next c2
      End If

      RANGECAT = cel
      End Function”

      The “combineText = cel” returns the value found. Further ranges can be added as needed (using “Optional rng3 as range, …” and repeating the rng2 for loops).

      Hope it helps

      Reply

  9. Hi Puneet,

    I have 1 Query in VBA Example : Ship Mode : Instant Air
    And Customer Name’s : Jeremy Lonsdale
    Cindy Schnelling
    Susan Vittorini
    Toby Braunhardt
    Ralph Arnett
    Harold Engle
    Helen Abelman
    Guy Armstrong
    Jennifer Braxton
    Giulietta Baptist

    And I need the result is Instant Air
    Roy Skaria, Jeremy Lonsdale, Cindy Schnelling, Susan Vittorini, Toby Braunhardt, Ralph Arnett, Harold Engle, Helen Abelman, Guy Armstrong, Jennifer Braxton, Giulietta Baptist, Erica Bern, Christopher Schild, Joy Smith, Evan Minnotte, Jenna Caffey,

    Like this Instant Air Would be one line remaining all are in one line with commas how it is possible can you let me know.

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

      Reply

      • 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?

        Reply

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

      Reply

  15. Dear Puneet,

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

    Reply

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

    Reply

    • 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?

      Reply

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

    Reply

  26. This is great!

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

    Thanks.

    Reply

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

    Reply