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?

Solution

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.)

Comments

[ashleedawg]

Which version of Excel?

[Lee Blake]

Any version. I'm currently using Libreoffice calc 5.1.1.3. I use Excel at work, though I'm not sure of the actual version.

[ashleedawg]

I've never used LibreOffice so I have no idea whether the functionality is the same in any of these examples. (Did you know that as a developer or a student, you can get Office 365 for free along with other goodies? ...not a trial; totally free from Microsoft.)

[Lee Blake]

My apologies. I am aware of these functions that can be used in formulas. By built-in function, I meant one automated by the software similar to the Text-to-columns function is built-in.

[ashleedawg]

What do you mean? "automated by the software"? maybe you should give some examples of what you have and what you need before anyone spends any more time on this.

[ashleedawg]

I don't know - I'm also having some trouble with Excel's functions not being automated as much as I'd like... I keep running CLEAN() and FIXED(), over and over, but still nothing's getting done around the house. I'm not having much more luck with DOLLARS() either. This is false advertising.

[Lee Blake]

In spreadsheet software, there's the text-to-columns function built in (in the menu). Like I specifically mentioned in the question, I'm aware of methods to achieve the same result using formulas. There are formulas I can use within the cells to achieve the "text-to-columns" as well, but Excel, Calc, etc. have this feature built in. Why do they no also have built in functions to do the reverse?

[Lee Blake]

Jim, spreadsheet functions work perfectly well to perform "text-to-columns" too, but that functionality is built in, why not in reverse as well?