Quickly combine text in multiple cells using this trick! [Formulas]

Posted on January 13th, 2014 in Excel Howtos - 49 comments

Ever wondered how to go from a bunch of cells with text to one big combined text? Like this:

Combine text values from multiple cells to single value using CONCATENATE & TRANSPOSE Functions - Excel trick

Well, there is a simple trick, shared by Grant with us in the What is the coolest Excel trick you learned in 2013? post.

Quick and easy way to combine bunch of text values

  1. Let say the cells you want to combine are in B2:B19.
  2. In a blank cell, where you want to concatenate all the values type
  4. Don’t press enter yet.
  5. Select the TRANSPOSE(B2:B19) portion and press F9. (related: debugging formulas using F9 key)
  6. This replaces the TRANSPOSE(B2:B19) with its result
  7. Now remove curly brackets { and }
  8. Enter
  9. Done!

See this demo to understand.

Combine text values using CONCATENATE, TRANSPOSE and F9 key - demo

Bonus tricks

  1. If you cannot use F9 for any reason, use CTRL+=
  2. If you want to add a delimiter (like space or comma) after each item in the text, you can use TRANSPOSE(B2:B19 & ” “) or  TRANSPOSE(B2:B19 & “,”)
  3. If the range you want to concatenate is across columns (Say A1:K1), then you can skip the TRANSPOSE formula and write =CONCATENATE(A1:k1), Select A1:K1 and press F9, remove {}s.

Keep in mind

Since F9 replaces formulas with values, if your original data changes, then you must re-write the CONCATENATE(TRANSPOSE(…)) again.

If you would rather keep the formulas alive, then use CONCAT() UDF. It takes a range and a delimiter and spits out combined text with ease.

More on dealing with text using Excel

Here are a few more tips on working with text values in Excel.

Thank you Grant

Thanks Grant for sharing this trick with all of us. It is a time saver for sure.

If you like this tip, say thanks to Grant. Also, in the comments, tell us how you combine text values and what other tricks you use.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

49 Responses to “Quickly combine text in multiple cells using this trick! [Formulas]”

  1. Rahim says:

    Great !
    Awesome :) Thanks for sharing

  2. Finnur says:

    Fantastic! This will save me a huge head ache in the future. Thank you.

  3. Michal says:

    In my excel version (2010, polish), is giving “\” instead of commas….

    • christoph says:

      My excel gives me only “.” not “;”.
      (excel 2010 german)

    • Hui... says:

      The use of ; and , are used predominantly in English only
      You will need to customise to suit your language

      To find out what your locale is

      In a cell Type =A1:E1
      Don’t press Enter but Press F9
      Excel will display something like ={1,1,1,1,1}
      Take a note of the separator character which is , in English

      In a cell Type =A1:A5
      Don’t press Enter but Press F9
      Excel will display something like ={1;1;1;1;1}
      Take a note of the separator character which is ; in English

      Note in above the ,’s seperate Columns and the ;’s separate Rows

      Use whatever Excel shows you in your language

    • Thijs says:

      Strangest Excel behaviour:
      In Excel 2003 and 2010, combining text from cells in a row works perfectly with the trick described by Chandoo. When combining text from cells in a column, skip the whole “transpose” thing. Just type =CONCATENATE(A1:K1), press F9 and remove the {} brackets, that’s it.

      Seems to be just the other way around in Excel 2013…

      • Greg G says:

        That doesn’t quite work because just using CONCATENATE results in

        The ;’s will not work.. using TRANSPOSE with CONCATENATE makes them ,’s instead, which does work. (See Hui’s post regarding ,’s and ;’s as separators (,’s for columns, ;’s for rows)

  4. Uday Kumar Chintada says:

    Wonderful technique.. Welldone !

  5. JLeno says:

    Nice trick! I always use the following trick though:
    1. In C2, enter =C1 & B2
    2. Copy down to C19 (C19 now has the info you want)
    3. Copy C19 and paste as values and delete the rest

    I think I still prefer this, since you have more flexibility when, for instance, the words don’t have end with a space character. In that case you can just type =C1 & B2 & ” “

  6. mike says:

    This is great!
    For some reason it is vital to leave out the last round bracket in the TRANSPOSE function before presing F9 button, otherwise it won’t work.

  7. MF says:

    Thanks for sharing that.
    I have been using a similar approach for doing the same thing.
    Let’s see which one is of more convenient to you.
    Using the example the above example:
    1) In a blank cell, Input =B2:B19 (do not press enter)
    2) Press F9
    3) Remove the “=”, “{“, “}” manually
    4) Press Ctrl H (Find and Replace)
    5) Find ” and replace with nothing
    6) Find ; and replace with your delimiter
    7) Enter
    8) Done

  8. Abhilash VK says:

    Great Tip Grant. Thanks for sharing Chandoo… I will publish it in my blog http://www.exceltoxl.com too.

  9. Lori says:

    I’d just copy the data to the formula bar using the office clipboard…
    1. click arrow in bottom right corner of clipboard section of ribbon.
    2. select B2:B19 and choose Copy
    3. select new cell and click the formula bar
    4. click the icon with the copied data from the clipboard
    5. Choose Replace… Find: [Ctrl+j] Replace [ ] OK.
    (The last step replaces line breaks with spaces or required delimiter.)

  10. Joyce says:

    Awesome Tricks.

  11. Tom says:

    This does not work in Excel 2010 as described in the posting or the responses.

  12. @Chandoo This is a great tip! I didn’t know this. Thx.
    @JLeno I use your method as well when I need to add a comma or space.

  13. Anup says:

    Cool indeed Grant!.

  14. Hui... says:

    I like this
    Thanx Chandoo

  15. Rudra Sharma says:

    Thanks Chandoo. Very nice tip. How did you discover this?

  16. LeonK says:

    This is an excellent article – Thank you Chandoo. I often need to do this in my work but have an alternative method for relatively short text concatenations using the JUSTIFY feature from within the EDITING ribbon. It is found by clicking the down arrow to the right of FILL and selecting JUSTIFY, at the end of the presented list.

    To use this feature, select all the text plus the column immediately to the right eg: if text was A1:A10, you would be selecting A1:B10. Now select the arrow to the right of the FILL command in the EDITING panel. Choose JUSTIFY. The text has now been concatenated, with spaces but probably into several rows. To place the text into only one cell, either widen the right hand column to the width you believe the text would fill and repeat the process. Alternatively, you could select a larger number of columns and then select JUSTIFY. This would place as much text as it could in the first cell, with the remainder in the cells below.

    The JUSTIFY command has the advantage of being fully ‘UNDO’able and can be coded in VBA to create a tool which might insert a new column to the right, justify selected text and then clean up the screen.

  17. kasusx says:

    thanx for sharing

  18. Ganesh Karra says:

    COPY all the list [B2:B19] in EXCEL
    SELECT all and copy from Notepad
    Paste in Excel Cell {edit}/ [F2] PASTE.

    ” Try This “

  19. jehangir says:


  20. Danail says:

    Awesome! thank you for sharing! you’ve just saved me bunch of time!

  21. Narendra says:

    Very nice !
    I love it

  22. rajiniakanth says:

    beautiful tips, thanks grant & chandoo

  23. […] Quickly combine text in multiple cells using this trick! Formulas: Useful Excel wizardry […]

  24. […] of creating a long concatenation formula, Chandoo shares a quick trick for combining the values in multiple […]

  25. Joyce Swensson says:

    Thank you. You just made my week.

  26. Brian Canes says:

    Please watch short vid http://bit.ly/ConcTran

    This uses the original tip but comes up with a formula using cell references. Note that this uses & as well as concatenate so that there is one argument in Concatenate for each cell reference and a separator. But the same method applies by using b,c5 instead of b&c5 but now there will be a separate entry for each ref and for each separator.


  27. Ying says:

    I love this trick/post very much! Coincidently, I recently run into a similar situation, but with repetitive groups all one column. I need to concatenate every 8 rows into one cell, and there are about 2000 rows, and only one column of text and number. How can I use this trick for every 8 rows? Without entering this formula 2000/8= 250 times? Thank you!

  28. Bibi sin says:

    Everybody also could try “phonetic” to complete this like “=phonetic(B2:B19)”

  29. Uday Kumar Chintada says:

    This trick works well if we are selecting the values (to be concatenated) from one particular column (say A1:A30). All the values are present in column A and this is where the transpose function takes it’s eminence. But if we want to do the same thing in a row (say row 1, data from A1:K1), this trick throws an error because the seperator would be ; for traspose function.

    We can overcome it by just removing the transpose function and make do with concatenate function only by following the same steps.
    i.e.; =concatenate(A1:K1), now select A1:K1, press F9 and replace curly braces with ( ). There you go.. Enjoy..

  30. mbsmarty says:

    Great! Thanks buddy :)

  31. Selly says:

    Thank you so much Grant. This really helped me with my school work

  32. Jason says:

    That’s one way to do it, I suppose.
    Really, the issue is that the =CONCATENATE function that comes with Excel is pretty useless. For just 3 fields, you can just as easily type =A1&A2&A3 as
    =CONCATENATE(A1, A2, A3)

    Years ago, I wrote the below to address this issue.
    The format is =CONCAT(A1:A3)

    Function concat(avec As Variant, Optional CHAR2INS As String, Optional trim As Boolean) As String
    ‘ this improves on the =concatenate function
    ‘ Arguments are:
    ‘ AVEC – this is the vector to be concatenated
    ‘ CHAR2INS – is the charachter to insert between cell items. If this is
    ‘ Left blank, Nothing Is inserted
    ‘ TRIM – whether to trim excess CHAR2INS
    Dim i, j, counter, Total As Integer
    Dim numrows As Integer
    Dim numcols As Integer
    Dim Temp As String
    Temp = “”
    numrows = avec.Rows.Count
    numcols = avec.Columns.Count
    Total = numrows * numcols
    For j = 1 To numrows
    For i = 1 To numcols
    ‘ skip blanks
    If avec(j, i) = “” Then
    GoTo 99
    End If

    ‘ don’t put a character after the last item
    counter = counter + 1
    If counter = Total Then
    Temp = Temp & avec(j, i)
    Temp = Temp & avec(j, i) & CHAR2INS
    End If
    99 Next i
    Next j

    ‘ this line takes out unprintable characters
    concat = Application.trim(Temp)

    If trim = True Then
    concat = Application.trim(Temp)
    concat = Temp
    End If

    End Function

  33. Marium says:

    This is really helpful :)
    Thank you..!!! ;)

  34. Casey E. Palmer says:

    Thanks for this! Saved me hours of work!

  35. Thiru says:

    Thanks so much for letting us know about this trick!! :-)

  36. Fred says:

    I didn’t know that concatenate has a limit for the number of arguments until I hit that number and was told I have typed in too many arguments in the function. Is there an alternative for this?

    • Hui... says:

      Instead of =Concatenate(A1, A2 …A100)
      you can use =A1&A2&A3 … &A100

      or you can use a UDF like those described at:
      Read the comments as well as there are several great additions to the basic code
      eg: =Concat(A1:A100,”,”) which will concatenate the values from A1 to A100 with a Comma in between each character
      =Concatif(B1:B5,A1:A5,1) which will concatenate the values from A1 to A5 where the corresponding cell in B1:B5 is = 1 etc

  37. Carlos says:

    Thank you so much!!!

Leave a Reply