How to merge rows in a column into one cell in excel

Question

E g   A1 I A2 am A3 a A4 boy    I want to merge them all to a single cell  Iamaboy  This example shows 4 cells merge into 1 cell however I have many cells  more than 100   I can t type them one by one using A1  amp  A2  amp  A3  amp  A4 what can I do

User · Answer

I use the CONCATENATE method to take the values of a column and wrap quotes around them with columns in between in order to quickly populate the WHERE IN    clause of a SQL statement   I always just type  CONCATENATE     B2          and then select that and drag it down  which creates  CONCATENATE     B3            CONCATENATE     B4           etc  then highlight that whole column  copy paste to a plain text editor and paste back if needed  thus stripping the row separation  It works  but again  just as a one time deal  this is not a good solution for someone who needs this all the time

User · Answer

For those who have Excel 2016  and I suppose next versions   there is now directly the CONCAT function  which will replace the CONCATENATE function    So the correct way to do it in Excel 2016 is      CONCAT A1 A4    which will produce        Iamaboy   For users of olders versions of Excel  the other answers are relevant

User · Answer

Inside CONCATENATE you can use TRANSPOSE if you expand it  F9  then remove the surrounding   brackets like this recommends   CONCATENATE TRANSPOSE B2 B19     Becomes   CONCATENATE  Oh    combining     a            You may need to add your own separator on the end  say create a column C and transpose that column    B1 amp      B2 amp      B3 amp

User · Answer

If you prefer to do this without VBA  you can try the following    Have your data in cells A1 A999  or such  Set cell B1 to   A1  Set cell B2 to   B1 amp A2  Copy cell B2 all the way down to B999  e g  by copying B2  selecting cells B3 B99 and pasting    Cell B999 will now contain the concatenated text string you are looking for

User · Answer

Use VBA s already existing Join function  VBA functions aren t exposed in Excel  so I wrap Join in a user-defined function that exposes its functionality  The simplest form is   Function JoinXL arr As Variant  Optional delimiter As String             arr must be a one-dimensional array      JoinXL   Join arr  delimiter  End Function   Example usage    JoinXL TRANSPOSE A1 A4          entered as an array formula  using Ctrl-Shift-Enter         Now  JoinXL accepts only one-dimensional arrays as input  In Excel  ranges return two-dimensional arrays  In the above example  TRANSPOSE converts the 4  1 two-dimensional array into a 4-element one-dimensional array  this is the documented behaviour of TRANSPOSE when it is fed with a single-column two-dimensional array     For a horizontal range  you would have to do a double TRANSPOSE    JoinXL TRANSPOSE TRANSPOSE A1 D1      The inner TRANSPOSE converts the 1  4 two-dimensional array into a 4  1 two-dimensional array  which the outer TRANSPOSE then converts into the expected 4-element one-dimensional array      This usage of TRANSPOSE is a well-known way of converting 2D arrays into 1D arrays in Excel  but it looks terrible  A more elegant solution would be to hide this away in the JoinXL VBA function

User · Answer

I present to you my ConcatenateRange VBA function  thanks Jean for the naming advice     It will take a range of cells  any dimension  any direction  etc   and merge them together into a single string  As an optional third parameter  you can add a seperator  like a space  or commas sererated    In this case  you d write this to use it    ConcatenateRange A1 A4      Function ConcatenateRange ByVal cell range As range                        Optional ByVal separator As String  As String  Dim newString As String Dim cell As Variant  For Each cell in cell range     If Len cell   lt  gt  0 Then         newString   newString  amp   separator  amp  cell      End if Next  If Len newString   lt  gt  0 Then     newString   Right  newString   Len newString  - Len separator    End If  ConcatenateRange   newString  End Function

User · Answer

For Excel 2011 on Mac it s different   I did it as a three step process    Create a column of values in column A  In column B  to the right of the first cell  create a rule that uses the concatenate function on the column value and      For example  assuming A1 is the first row  the formula for B1 is  B1  For the next row to row N  the formula is  Concatenate     A2   You end up with      QA  Sekuli  Testing  Applitools  Visual Testing  Test Automation  Selenium    In column C create a formula that concatenates all previous values   Because it is additive you will get all at the end  The formula for cell C1 is  B1  For all other rows to N  the formula is  Concatenate C1 B2    And you get     QA Sekuli QA Sekuli Testing QA Sekuli Testing Applitools QA Sekuli Testing Applitools Visual Testing QA Sekuli Testing Applitools Visual Testing Test Automation QA Sekuli Testing Applitools Visual Testing Test Automation Selenium   The last cell of the list will be what you want   This is compatible with Excel on Windows or Mac

User · Answer

In simple cases you can use next method which doesn t require you to create a function or to copy code to several cells    In any cell write next code    Transpose A1 A9      Where A1 A9 are cells you would like to merge    Without leaving the cell press F9   After that  the cell will contain the string      A1 A2 A3 A4 A5 A6 A7 A8 A9    Source  http   www get-digital-help com 2011 02 09 concatenate-a-cell-range-without-vba-in-excel   Update  One part can be ambiguous  Without leaving the cell means having your cell in editor mode  Alternatevly you can press F9 while are in cell editor panel  normaly it can be found above the spreadsheet

User · Answer

I know this is really a really old question  but I was trying to do the same thing and I stumbled upon a new formula in excel called  TEXTJOIN    For the question  the following formula solves the problem   TEXTJOIN    TRUE  a1 a4     The signature of  TEXTJOIN  is explained as TEXTJOIN delimiter ignore empty text1  text2   text3

[excel] How to merge rows in a column into one cell in excel?

Examples related to excel

Examples related to vba