Reversing Text to Columns in Excel/Calc

Question

Most spreadsheet software I've used has a text-to-columns function that splits a single column into multiple columns using several criteria (delimiter, # of character, etc). I want to do this in reverse.

I know I can use a formula to create a third column with the values of the two cells concatenated together with a delimiter between them but this seems clunky.

Why is this not a built in function? Or is it there somewhere that I can't see?

Answer 1

There are built-in functions that will allow you to combine multiple cells into one with a delimiter between each.

Let's say you want to join cells A1 to A5 with a , in between each value.

---

Excel 2016

=TEXTJOIN(",",TRUE,A1:A5)

---

Older Excel Versions

You can easily combine without a delimiter:

=CONCAT(A1:A5)

The equivalent to concat is the & text operator, which you could use like:

=A1 & A2 & A3 & A4 & A5

...for the same result as CONCAT. To add the comma delimiter:

=A1 & "," & A2 & "," & A3 & "," & A4 & "," & A5

---

Replacement for TEXTJOIN function

Better yet, we could build our own version of TEXTJOIN, which I'll call TXTJOIN:

Function TxtJoin(delim As String, ignoreEmpty As Boolean, rg As Range) As String
    Dim c As Range
    For Each c In rg
        If Not ignoreEmpty Or Not IsEmpty(c) Then TxtJoin = TxtJoin & delim & c
    Next c
    If TxtJoin <> "" Then TxtJoin = Mid(TxtJoin, Len(delim) + 1)
End Function

The first parameter delim is the delimiter to place between each value, and can be one or more characters, or even special characters like CHAR(10) for a Line Feed (which would display in cells that have Word Wrap enabled.)

The second parameter ignoreEmpty can be FALSE if you want blank cells included in the results, with a delimiter between each one, or TRUE to skip blank cells. (Obviously if the specified range has no blank cells then it doesn't matter what option you choose.)

The third parameter is a range of cells. If there's more than one row or column specified, the formula will read right-to-left then top-to-bottom.

With our example, you'd use it like:

=TxtJoin(",",TRUE,A1:A5)

(This is the same usage as for Excel 2016's TEXTJOIN function.)