Substitute textjoin formula not working for final cell

Question

I'm trying to remove the unique values after joining cells in Excel. It's a list of phone numbers that I combine into a single cell after I separate them with a comma. I'm using this formula:

=SUBSTITUTE(TEXTJOIN(",",TRUE,$I$19:$I$28),I19&",","")

For whatever reason, it properly applies to every cell in the range except for the last one; meaning that the last phone number still shows up in the final list. So for example, if I have numbers 12345 23456 34567 and 45678, it should come out like this:

23456,34567,45678    
12345,34567,45678    
12345,23456,45678    
12345,23456,34567

but it comes out like this

23456,34567,45678    
12345,34567,45678    
12345,23456,45678    
12345,23456,34567,45678

Any ideas why this might be happening?

Answer 1

It is looking for 45678, to substitute with "". There is no , at the end. So we force one and then remove it at the end:

=LEFT(SUBSTITUTE(TEXTJOIN(",",TRUE,$I$19:$I$28) & ",",I19&",",""),LEN(SUBSTITUTE(TEXTJOIN(",",TRUE,$I$19:$I$28) & ",",I19&",",""))-1)

Or put the comma on the front and use MID:

=MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$I$19:$I$28),"," &I19,""),2,999)

Or you can use TEXTJOIN in an array form:

=TEXTJOIN(",",TRUE,IF((ROW($I$19:$I$28)<>ROW(19:19))*($I$19:$I$28<>""),$I$19:$I$28,""))

Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Then copy/drag it down like you do with the first.

---

The second, using the MID is the better of the choices.