fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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
  3. =CONCATENATE(TRANSPOSE(B2:B19))
  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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

181 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
        =CONCATENATE({"oh";"combining";"a";"bunch";"of";"texts";"into";"one";"big";"texts";"is";"so";"painful";"well";"not";"any";"more"})

        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)

    • dave says:

      After the TRANSPOSE command, with the curly brackets still in the result, you can do find - replace and replace any / ; . into a ,
      Thereafter finalize the Concatenate formula and your text should be fine.

      • Martin says:

        Yeah !!
        That's it!
        Dave you're great!
        Gee, it was so simple :):)
        Thx

        • Martin says:

          And that's right what Hui… said
          When you click F9 having formula with column array (eg. =A1:A20) you'll get ";" between values
          When you hit F9 having values in row (eg. =A1:E1) you'll get diffrent character ("\" or ",") between values

          So for me it's just about to use the trick
          with CONCATENATE and TRANSPOSE formulas, when I want to combine values from column,
          and to use CONCATENATE just with an array (then hit F9 on it), when values are in row.

          • Martin says:

            whoops 🙂
            Was wrong 😛
            CONCATENATE with TRANSPOSE when they're in a row
            and CONCATENATE without TRANSPOSE when they're in a column

    • Priyank Gupta says:

      Hi Michal,

      While doing concatenate..I am getting "\" instead of ;.Could you please let me know how you resolved your issue?

      • Nelson says:

        Just add or remove TRANSPOSE according to what you tried first. It happened to me; it seems that depending on your regional settings it can work in the opposite way.

  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.

    • Chandoo says:

      @Tom... I have tested it with 2010 too, it is working alright. Can you tell me which locale you are using? I am using English (EN-US).

      • Sreekanth says:

        It is not accepting if number of rows increased. It gives me error "More arguments have been specified for this function than are allowed in the current file format". Please give me solution for this.

        • Leisa says:

          I think that the problem may be that there is a character limit for the cell in which you have placed the formula. (I was doing this off of some customer email data in a table form for which I want to do a simple cut and past from the data.

          My fix will be to simply parse my data (I have 500 rows) to allow working within the cell content limits. Still saves a ton of work. Chandoo rocks!

  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:

    great.....
    thanx for sharing

  18. Ganesh Karra says:

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

    " Try This "

    • Jack says:

      Ganesh Karra says:
      January 17, 2014 at 3:45 am
      COPY all the list [B2:B19] in EXCEL
      PASTE it in NOTEPAD
      SELECT all and copy from Notepad
      Paste in Excel Cell {edit}/ [F2] PASTE.

      " Try This "

      Hi Ganesh,

      Can you be more specific in your reply? Thanks!

  19. jehangir says:

    x-cellent

  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.

    Regards
    Brian

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

    • Roshan says:

      Hi,
      Thanks for this trick for concatenating in columns. How to add any delimiter then like "/" or comma?

      I have a sheet of 30+ columns. Using Transpose first for adding delimiter creates problem with ; error. and with your way, i can't add any delimiter. Please help

  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)
    Else
    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)
    Else
    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:

      @Fred
      Instead of =Concatenate(A1, A2 ...A100)
      you can use =A1&A2&A3 ... &A100

      or you can use a UDF like those described at:
      http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
      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!!!

  38. Cathy says:

    Wow Amazing, very handy.

  39. Syed Bilal says:

    Great Man,
    Thanks!

    BR/Syed Bilal

  40. Reina says:

    Hi, what if the information in the cells are numbers? I tried using this trick A1&B1 but the numbers can't be added anymore using AutoSum. In other words, the numbers weren't considered as numbers by Excel. What do I do?

    • Hui... says:

      @Reina
      When I use =A1&A2 with 1 in A1 and 2 in A2 I get 12 which is what I should get
      can you explain what / how your not getting the same answer?

      • Reina says:

        Hi, I finally found the answer. I just had to put =VALUE(A1&B1) so that the information in the cells will still be considered as numbers.

  41. Rohit says:

    Hi thanks for wonderful examples.

    I want to combine text strings from different cell with ,'s as delimiter and 'and' before the last text.

    Forexmaple a, b, c, and d

    Regards
    Rohit

  42. Shal says:

    Brilliant!!! Thx for sharing!

  43. Alusi says:

    Awesome Chandoo - You are my Excel Hero. Thanks for sharing

  44. Rachael says:

    Worked a treat. Many thanks, Rachael

  45. J says:

    Delimeters do not work, just throws up an error.

    • J says:

      Worked it out. The example that looks like this:

      TRANSPOSE(B2:B19 & “,”)

      Write it like this instead:

      TRANSPOSE(B2:B19&“,”)

  46. CL says:

    WOW! Excellent trick!!! Thank you!

  47. E.L. Cropredy says:

    Thank you so much -- exactly what I needed

  48. Damodar says:

    Thanks.. Its worked for me and reduced lot of time 😀

  49. hello, i've seen and using all this examples and methods and are very useful but in my case it doesn't mach what i need it for i you guys can give me hand here...

    i need to create a list of attributes in just one cell separating each other with an ":" using the a2:cv2 just notice that in some of the columns are empty because the filter in that product doesn't apply.
    for example

    a2= black, b2= , c2= small... cv2= cube

    so this should look like this at the end. "Black:Small:Cube"

    but with formula =concadenate(a2:cv2&":")
    = "Black::small::::::::::::::::::::::::::::::::::::::::::::::::cube:"

    so i'm looking to do this for each row taking in consideration each column selected to create the right attributes applied for the product and no extra space (knowing that an extra space is taked for my upload system that each ":" is a new attribute ) and also when i do this excel make an auto correct option but not copying the formula and just the result in all the rows and i need to make a copy of the original formula and make it valid per each one on the following 3k rows..

    Thanks for your collaboration
    😀

  50. Ali says:

    This is very neat. What's the delimiter for a new line in Excel 2013? Basically in the above example replacing the & "," with a new line delimiter? I want every column value (each word) to be in a separate line in the same cell.

  51. ajay kumar says:

    how can we use F9 in muti cells together ...i have 100 cells and every cell has array which is working fine but i have to go to each cell and press f9 to see the result and then concatenate. can we not use F9 just once for all cells and then do the rest thing

  52. Rahul says:

    Can someone suggest shorter way of Exactly opposite of what Chandoo made us learn here, i.e. Seperate text given in "different rows in a single (same) cell (separated by ALT+ENTER) into different rows altogether"?

  53. saranya says:

    Its useful

  54. hayder says:

    thank you so much , same function is sold online , is there another trick to do the opposite ? i have many values in one cell separated only by space , I tried the column to text but no use , thank again

  55. Jenny says:

    I was so excited when I saw this and thought how perfect it would be for me to put together the missing records for my IRS file. Alas, since the file record is 750 characters long, and each character is in a row of its own, I have too many arguments for the function.

    Guess I'll get lots of practice doing it in little bites, then combining those little bites into bigger and bigger bites, until it's one long record. Curse you IRS!

  56. hemraj says:

    Nice article

  57. I need t to merge rows in one of my excel sheets, so after long googling this simple function has been born and I name it MergeCells(). Calling it is simple, select the range you want to merge, and set the delimiter.
    Example:
    MergeCells(A23:Q23, "-")

    [vb 1="vbnet" language=","]
    Public Function MergeCells(cellsRange As range, delimiter As String) As String
    Dim cell As range
    Dim mergedCells As String

    mergedCells = ""
    For Each cell In cellsRange
    mergedCells = mergedCells & delimiter & cell.Value
    Next cell

    MergeCells = Right(mergedCells, Len(mergedCells) - 1)
    End Function
    [/vb]

  58. Pranitha says:

    When I try this I get a Value error if i dont do the F9. It works with f9, but dont want to write the formula for 300 rows. can anyone help. its 2007
    the formula i used is = CONCATENATE(A4:U4 & ",")

    Also, In that row, few cells have no value, and few are numbers and text. Is that a problem?

  59. rosa says:

    holy carp I'm in love..! Thanks!!!

  60. fisho says:

    Chandoo, Great tip!

  61. Monica says:

    Thank you SO MUCH for this tip. I've been so frustrated with how the CONCATENATE command won't accept a range of cells, and this is quick and easy to implement, even with the ;--, substitution. You made my life a lot easier!

  62. Mangs says:

    Thanks a lot bro, helped me save lot of time.

  63. Ayush Srivastava says:

    I have another issue with concatenate .. hoping some one can help ...
    data is in the below form -

    112233 SAP BO
    112233 SAP BW
    123456 Teradata
    123456 Qlikview
    123456 Talend DI

    like above example a huge data with employee IDs in one column and Skill name in another column.
    number of times the employee id repeats itself is not constant.
    Is there some way i can concatenate using some loop or logic.
    Please help.

  64. Sabrina says:

    Hi there,
    This was very helpful... Do you have a solution if i am trying to concat a string across many worksheets?

    For example, cell B4 on Sheet1 would concatenate all the text from cell B4 on Sheets 2:8.
    can anyone help?

    thanks

  65. Bob says:

    Thank you so much. I wanted to concatenate a large number of postcodes for use on a survey, and now thanks to you I don't have to enter them all.

    Awesome work!

  66. Brad says:

    Hi, what VBA code do you amend to include a delimiter of ","

    Kind Regards

    Brad

  67. Pablo says:

    This tip is great! Saves a lot of time! Thanks

  68. Amberly says:

    THIS IS AWESOME!

  69. Chika says:

    You freaking saved me! Awesomeeeeee!!!!!!

  70. codecorrect says:

    nice!

  71. Tanya says:

    I searched all around and this was the best resource. THANKS!!!!

  72. Priya says:

    Great . Thanks alot

  73. Bronwen says:

    Hi there

    the original formula is very useful and thanks for sharing.

    I have a spreadsheet where I need to apply this to over 3,000 rows where the following:

    Column A Column B (the information I'm concantenating)
    XXXXX1 5 rows
    XXXXX2 10 rows
    XXXXX3 15 rows

    Result:

    XXXXX1 (information from 5 rows)
    XXXXX2 (infomation from 10 rows)
    etc

    How do I create the formula and apply it to the whole spreadsheet. What I'm getting stuck on is column B has different amounts and I'm not sure how to copy a formula and apply it again when you need to press F9 and remove brackets.

    Any help would be greatly appreciated.

  74. Prany says:

    Looking for help turning this:
    RR686
    VK165
    K2418
    L7192
    P0636
    P4303
    P4802
    P6084
    P7390
    P8058

    Into these:
    'RR686'.'VK165'.'K2418'.'L7192'.'P0636'.'P4303'.'P4802'.'P6084'.'P7390'.'P8058'.

  75. Martin says:

    OMG Thanks! Saved me a lot of time, much better than the VBA approach suggested elsewhere.! Thanks!

  76. Avinash says:

    Thanks for this trick. it unique trick not able to find anywhere.

  77. Joe Wilson says:

    This awesome, thank you 🙂

  78. Muhammad Zubair says:

    Really nice tipss..

  79. Ganesh says:

    superb

  80. ashwith says:

    Nice tric chandoo but . in my case its slightly different I don't want add letter which is once added/I don't want repeat same letter/word once its added using this formula.. which formula I can use.. Pls help

  81. ashwith says:

    Nice tric chandoo .but in my case its slightly different I don't want add letter which is once added.I don't want repeat same letter orsame word should show more than once ..which formula I can use.. Pls help

  82. Alex says:

    Thanks, Grant! You saved me so much time!! I already spent quite a while trying to find an answer to this dilemma until I came across this article through Google search. Excel support ? was not very helpful. You're the best!

  83. […] you don’t have a lot of rows to combine, you can use the CONCATENATE function. Here is an article that shows how that function can be used. The values in that article are split across rows, not […]

  84. Dinesh says:

    Thanks Chandoo. Awesome trick 🙂

  85. Ant says:

    Very nice trick indeed! Got a question.

    I got something like this:

    23 image.jpg
    23 image2.jpg
    23 image3.jpg

    24 image12.jpg
    24 image33.jpg
    24 image23.jpg

    With the formula given here, it works great to add all 23s values (images) in one cell. Is there any way that I can put a formula or value to take automatically the next batch (24s values) for putting them together in a cell below the 23?

  86. sam says:

    hi, i have the 50,000 rows of the following data in separate columns:

    name of company, unit no., block no., block name., postal code.

    need to combine all data into the following format:

    name of company
    unit no
    block no. and block name
    postal code

    does anyone know how to combine cells but format the info to be in different lines?

  87. May Hee says:

    Awesome...Live saver...Thank you.

  88. Hemant says:

    HI , great post .
    i also have some kind of same requirement
    i have the data
    edit IP_ 115.47.46.156 ( Both text are in diff. column i have merage them using {E6&F6} since they are in 6th row )

    also have some text which is in the different column
    set subnat 115.47.46.156 255.255.255.255 also merge them in same manner .

    what i want here
    i want both of above text in the following manner

    edit IP_115.47.46.156
    set subnat 115.47.46.156 255.255.255.255

    Both are in different column ......... How can i do this by using the formula ...

  89. ED Gil says:

    Once again this is another Friday where chandoo turns our horrible day into a smooth work shift 🙂

    Thank you you saved my day!!

  90. Frozen says:

    One Stop solution:

    =Concat("Range","Delimiter"

    Function CONCAT(useThis As Range, Optional delim As String) As String
    Dim retVal, dlm As String
    retVal = ""
    If delim = Null Then
    dlm = ""
    Else
    dlm = delim
    End If
    For Each cell In useThis
    If CStr(cell.Value) "" And CStr(cell.Value) " " Then
    retVal = retVal & CStr(cell.Value) & dlm
    End If
    Next
    If dlm "" Then
    retVal = Left(retVal, Len(retVal) - Len(dlm))
    End If
    CONCAT = retVal
    End Function

  91. Always Learning says:

    AMAZING! You just saved me light years of time. Thank you!!

  92. R Johnson says:

    Thank you so much for posting!

  93. Emre says:

    Cool trick. Thanks 🙂

  94. […] it is to concatenate more than two text values (although there are plenty of blog posts like this one showing how to do it). Well, not any more – Microsoft have finally got around to addressing this […]

  95. Rory says:

    Love this trick - best one I have seen for this task. Use this frequantly. Thanks

  96. Jayakumar Krishnamoorthy says:

    This is my formula to combine text which is present on Range A1:A241 (the range could accept only upto 241). I'm trying to record this into macro to automate, but the formula could not store in macro and showing in red color. How to record this formula as macro and use it?

    =CONCATENATE(A1,">"&A2,">"&A3,">"&A4,">"&A5,">"&A6,">"&A7,">"&A8,">"&A9,">"&A10,">"&A11,">"&A12,">"&A13,">"&A14,">"&A15,">"&A16,">"&A17,">"&A18,">"&A19,">"&A20,">"&A21,">"&A22,">"&A23,">"&A24,">"&A25,">"&A26,">"&A27,">"&A28,">"&A29,">"&A30,">"&A31,">"&A32,">"&A33,">"&A34,">"&A35,">"&A36,">"&A37,">"&A38,">"&A39,">"&A40,">"&A41,">"&A42,">"&A43,">"&A44,">"&A45,">"&A46,">"&A47,">"&A48,">"&A49,">"&A50,">"&A51,">"&A52,">"&A53,">"&A54,">"&A55,">"&A56,">"&A57,">"&A58,">"&A59,">"&A60,">"&A61,">"&A62,">"&A63,">"&A64,">"&A65,">"&A66,">"&A67,">"&A68,">"&A69,">"&A70,">"&A71,">"&A72,">"&A73,">"&A74,">"&A75,">"&A76,">"&A77,">"&A78,">"&A79,">"&A80,">"&A81,">"&A82,">"&A83,">"&A84,">"&A85,">"&A86,">"&A87,">"&A88,">"&A89,">"&A90,">"&A91,">"&A92,">"&A93,">"&A94,">"&A95,">"&A96,">"&A97,">"&A98,">"&A99,">"&A100,">"&A101,">"&A102,">"&A103,">"&A104,">"&A105,">"&A106,">"&A107,">"&A108,">"&A109,">"&A110,">"&A111,">"&A112,">"&A113,">"&A114,">"&A115,">"&A116,">"&A117,">"&A118,">"&A119,">"&A120,">"&A121,">"&A122,">"&A123,">"&A124,">"&A125,">"&A126,">"&A127,">"&A128,">"&A129,">"&A130,">"&A131,">"&A132,">"&A133,">"&A134,">"&A135,">"&A136,">"&A137,">"&A138,">"&A139,">"&A140,">"&A141,">"&A142,">"&A143,">"&A144,">"&A145,">"&A146,">"&A147,">"&A148,">"&A149,">"&A150,">"&A151,">"&A152,">"&A153,">"&A154,">"&A155,">"&A156,">"&A157,">"&A158,">"&A159,">"&A160,">"&A161,">"&A162,">"&A163,">"&A164,">"&A165,">"&A166,">"&A167,">"&A168,">"&A169,">"&A170,">"&A171,">"&A172,">"&A173,">"&A174,">"&A175,">"&A176,">"&A177,">"&A178,">"&A179,">"&A180,">"&A181,">"&A182,">"&A183,">"&A184,">"&A185,">"&A186,">"&A187,">"&A188,">"&A189,">"&A190,">"&A191,">"&A192,">"&A193,">"&A194,">"&A195,">"&A196,">"&A197,">"&A198,">"&A199,">"&A200,">"&A201,">"&A202,">"&A203,">"&A204,">"&A205,">"&A206,">"&A207,">"&A208,">"&A209,">"&A210,">"&A211,">"&A212,">"&A213,">"&A214,">"&A215,">"&A216,">"&A217,">"&A218,">"&A219,">"&A220,">"&A221,">"&A222,">"&A223,">"&A224,">"&A225,">"&A226,">"&A227,">"&A228,">"&A229,">"&A230,">"&A231,">"&A232,">"&A233,">"&A234,">"&A235,">"&A236,">"&A237,">"&A238,">"&A239,">"&A240,">"&A241)

  97. wow says:

    Wow! made my life way easier, thank you!

  98. Nipin Madan says:

    Hi

    Great trick dear.

    But it only works up to 8132 characters only so please share any other trick that will work for minimum 20000 rows and around more than 2-5 lakhs characters.

  99. Dana says:

    This is awesome!!! This solution saved my day! Many thanks!!!

  100. BAM BAM says:

    Wow, such a big help!

  101. Stephen says:

    This is great i think Microsoft should speak to you about using this as a standard formula

  102. Monica says:

    mama mia! so handy! thank you!!

  103. sreekhosh says:

    OMG...awesome 😀

  104. Monika says:

    Oh my God THANK YOU! You've no idea... 🙂

  105. David says:

    Hello - this is a great solution! However, Excel limits the number of characters in a formula to 8,192 characters. I have a list of id numbers that are 7 characters long that I want to combine into a single string. The list has 4,541 records. That's 31,787 characters plus more for spaces and comas - well above the Excel limit.

    Could this be done usng SQL?

    thanks again
    DW

  106. Ganesh says:

    Not able to working it. I have 419 rows some are blank & some are with numbers but getting error when Used CONCATENATE(TRANSPOSE

  107. Martina says:

    Many thanks for sharing!!! Very helpful and time saving 🙂

  108. Dinesh Baghel says:

    Hi All,

    This works fine - but if you copy this peace of code form excel to outlook and then again copy and paste from outlook to back to excel - you will feel it won't work successfully. Again it split the data into separate rows.
    So, Need to know the perfect solution when we do like this?

  109. Muhawat Ali says:

    You are Great Sir.
    Very Useful.

  110. mag says:

    Works beautifully!!!! Saved me sooooo much time. THANKS!!!! 🙂

  111. Ashraf says:

    Works perfectly, But vertically not horizontally .

  112. shailendra says:

    thanks a lot mate. wonderful solution

  113. arun says:

    I want to make a part of the result of concatenation without VBA. how? help please....

  114. arun says:

    I want to make a part of the result of concatenation BOLD or UNDERLINED or ITALICS without VBA. how? help please….

  115. Vj says:

    Worked like a charm.. Thank you so much..

  116. ERICA says:

    This was incredibly helpful. I think you shaved years off my work life (in a very good way). Thanks so much.

  117. can says:

    The best ever!

  118. Luke Martin says:

    Thanks for this trick. Saved a bunch of time!!!!

  119. Rashid says:

    Great...!!!

  120. Suresh says:

    Hi,
    Its very helpful tips...)
    Meanwhile, shall we also do for dates? for Eg: A1 to K1 I am having different dates, I need all these dates in L1, its possible?
    Thanks.

  121. Caroline says:

    Thanks!

  122. Jean says:

    When I try doing this, I don't get the spaces between the words. For example, when I try to mimic your example, the end result I get is: Ohcombiningabunchoftextsintoonebigtextissopainful!Wellnot anymore:). I have Microsoft Excel 2010. Any advice around this would be appreciated.

    • Hui... says:

      @Jean
      The words in the first column already have a space included at the end of each word
      ie: The first cell is "Oh ", not "Oh"

    • Hui... says:

      @Jean

      If you have access to Excel 2016 or beyond you can use the new function =Textjoin()

      eg: =TEXTJOIN(" ",TRUE,A2:A10)
      that will join all the text between A2 and A10 and place a space between the values from each cell, The True means it will ignore Blank cells

      • Jean says:

        Thanks Hui, however, I do not seem to have the TEXTJOIN function. Is there something special I need to do to get it? Thanks!!

        • Jean says:

          Actually, nevermind; I found a work around. I just added a comma and a space by doing this =A1 &"," & " ". Worked perfectly! Thanks! 🙂 I appreciate the help!

        • Hui... says:

          @Jean

          If you have access to Excel 2016 or beyond you can use the new function =Textjoin()

  123. Murali says:

    It saved my day! Fantastic tip! Thanks a lot!

  124. Maksud says:

    Dear, Please help to know as how to arrange data in one cell by use comma (,) from a list in one column such as
    from one column
    504020
    504020
    302080
    504020
    302080
    504020
    to in one cell
    302080, 504020
    Thanks/Maksud

  125. Effectsalive says:

    I generally make use Notepad++ to achieve this by using its unwrap text feature. However now with being able to do this directly in excel, can skip that step 🙂 Sweet!

  126. Anshul Sood says:

    You're super awesome... Thanks a tons!!

  127. Chalak says:

    Man you rock !! you saved me much time as i copied and arranged more than 2000 Cells with this trick
    Many thanks

  128. M B Sridharan says:

    Hi

    how can i Combine be giving some specific conditions....i have provided the comments for months in different cells i want combine the text for one month

  129. SMGILLANI says:

    very nice , thanks for sharing .

  130. SMGILLANI says:

    thanks for sharing very helpful

  131. mike John szczesny says:

    How can this be done in reverse? Say you have a one cell with many words. What formula can be used to show each single words being shown down a row (or column)?

  132. Sakthi says:

    This is very helpful. Thank you for sharing.

  133. Chris says:

    Guys,
    I have 20000 rows in one column in excel and I need to concatenate every 50 rows. I don't need a separator, I don't have a column header. After 50 rows are concatenated into one cell, I need the 49 remaining rows to be deleted so that in the end I have a column with 400 rows and in each row I have 50 original rows concatenated. I don't wanna do it 400 times. What's the easiest way to do this?
    Thank you!
    Chris

  134. Leila says:

    It was great .
    How it works if i had many columns instead of rows? ( concatenate 15 columns)

    Thanks

  135. Heidi says:

    I'm trying to turn a column of dates into a paragraph list of dates separated by commas. When I use this exact method described above, the layout comes out perfect, but all the dates turn into number values. Can anyone help me keep the dates displaying correctly?

    Date column looks like:
    8/24/21
    8/25/21
    8/26/21

    What I want it to look like is:
    8/24/21,8/25/21, 8/26/21

    What it's doing is:
    44432,44433,44434

Leave a Reply