Excel: Combining Cell Contents (Multiple Cells)

The following question was asked by a user on an Excel forum where I lurk on occasion:

I have an Excel file with data similar to this:
columnA          columnB
123                   ABC come
456                   to delete certain records
789                   again this is nice

The output I would like to have, in a single cell, would be like this:
123 ABC come
456 to delete certain records
789 again this is nice

The problem is that the number of rows and columns may vary. It is difficult for me to use CONCATENATE or =A2&B2 etc. Can I do this with a single formula or any other means?

Clearly, the questioner had clearly already considered and tried using the built-in Concatenate function and provided a data sample in which 3 rows and 3 columns had been concatenated into a single cell, sparated by Chr(10) – i.e. a new row where there was a new row in the data set. The problem was that the the functionality was required for any size group,  where the number of rows and columns might be quite different from those shown in the sample.

Essentially, what was required can’t be done with a native formula or function and requires a User Defined Function (UDF). Here is my proposed solution (which was accepted by the questioner on the forum). The function is shown below:

Function JoinAll(myRange As Range)

Dim n as Long
Dim x as Variant
n = myRange.Row

For Each x In myRange
If x <> "" Then
If x.Row = n Then
JoinAll = JoinAll & x & " "
JoinAll = JoinAll & Chr(10) & x
n = n + 1
End If
End If

End Function

Using this function will provide the result required in a single cell, as shown below:


However, this still requires the range to be added to the formula but, if this is going to be used on a regular basis and the data range is highly variable, the solution would be to combine it with a dynamic range e.g.


Now the cell range will recalculate dynamically with the range.

Job done.