4

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?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Lee Blake
  • 341
  • 1
  • 2
  • 15

2 Answers2

7

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

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • 1
    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](https://developer.microsoft.com/en-us/office) or a [student](https://www.microsoft.com/en-ca/education/products/office/default.aspx), **you can get *Office 365* for free** along with other goodies? ...not a trial; totally free from Microsoft.) – ashleedawg Apr 04 '18 at 04:57
  • 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. – Lee Blake Apr 04 '18 at 13:45
  • 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 Apr 04 '18 at 21:55
  • 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 Jul 31 '18 at 12:45
0

This works in LibreOffice 5.4.3.2. If it does not work in your version, you may want to download and install a newer version.

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

By built-in function, I meant one automated by the software similar to the Text-to-columns function is built-in.

I do not see such a feature. It is not needed because spreadsheet functions work perfectly well.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 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. – ashleedawg Apr 04 '18 at 22:02
  • Jim, spreadsheet functions work perfectly well to perform "text-to-columns" too, but that functionality is built in, why not in reverse as well? – Lee Blake Jul 31 '18 at 12:47