Is there an easy way to turn columns-to-text without CONCATENATE in Excel?

Question

I'm aware of the text-to-columns button. Can the opposite be done by selecting columns, and possibly running a macro or using a similar button?

Answer 1

Normally I say "Please post your code" for help/assistance writing macros, but this one is really, really simple.

Public Function ColumnsToText(rng As Range, Optional DelimitBy As String = " ") As String
'Applies on a row/partial row of data. Must be continuous cells, e.g., A1:D1.
Dim var As Variant

var = Application.Transpose(Application.Transpose(rng.Value))
    ColumnsToText = Join(var, DelimitBy)

End Function

ENter it in a worksheet cell like:

=ColumnsToText(A1:F1)

Or you can specify an optional delimiter, it is a space by default, but you could use any string characters:

=ColumnsToText(A1:F1,"%")

Etc.

Answer 2

I was intrigued by the answer @David Zemens gave using a double transpose. His answer works well. I did found one other way to get a single row into a 1 dimensional array. Also I wanted a function that could handle a column. So here is my alternate answer.

Public Function ColumnOrRowToText(rng As Range, Optional DelimitBy As String = " ") As String
    'rng must be a single row or a single column; cannot handle multiple columns or rows

    Dim var As Variant
    If rng.Rows.Count = 1 Then
        var = Application.WorksheetFunction.Index(rng.Value, 1, 0) 'for a single row
    Else
        var = Application.Transpose(rng.Value) 'for a single column
    End If

    'var must be a 1 dimensional array
    ColumnOrRowToText = Join(var, DelimitBy)

End Function

Answer 3

I believe the true reverse of Text-to-Columns is the formula TEXTJOIN. It will return your selected column entries into one cell in one long string separated by a delimeter that you can set on your own.