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 niceThe 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 niceThe 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 & " " Else JoinAll = JoinAll & Chr(10) & x n = n + 1 End If End If Next End Function

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

=JoinAll(A1:B3)

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.

=joinAll(myDynamicRange)

Now the cell range will recalculate dynamically with the range.

Job done.