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

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 & A2 & A3 & A4 what can I do?

Answer 1

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) <> 0 Then
        newString = newString & (separator & cell)
    End if
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(separator)))
End If

ConcatenateRange = newString

End Function

Answer 2

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&" "
=B2&" "
=B3&" "

Answer 3

If you prefer to do this without VBA, you can try the following:

1. Have your data in cells A1:A999 (or such)
2. Set cell B1 to "=A1"
3. Set cell B2 to "=B1&A2"
4. 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.