Is Textjoin inconsistent or am I doing it wrong again?

For six cells you can do it with a long formula like that but if you have more than that you should consider using a row of formulas to do most of the work. You can hide that row later. Below are two solutions.

The first method is in row 3 and it is brute-force like your formula.

Formula in AK2 =IF(CONCAT(AE2:AJ2)="", "", TEXTBEFORE(IF(AND(AE2="x",AE1≠""), AE1&", ","")&IF(AND(AF2="x",AF1≠""),AF1&", ","")&IF(AND(AG2="x",AG1≠""),AG1&", ","")&IF(AND(AH2="x",AH1≠""),AH1&", ","")&IF(AND(AI2="x",AI1≠""),AI1&", ","")&IF(AND(AJ2="x",AJ1≠""),AJ1&", ",""),", ",−1))

It does not use TEXTJOIN at all, it concatenates the cells using the & operator. It uses TEXTBEFORE to remove the final ", ". It uses CONCAT to first make sure there is at least one non-empty cell so that TEXTBEFORE does not return an error. This formula is okay if you actually have only 6 cells to check. If that number gets much larger or might change in the future I suggest a different method.

The second method uses a row to do most of the work. This is often what is required to replace an Excel array formula. Excel can do a row's worth of calculations within the formula itself, Numbers cannot.

AE3 =IF(AND(AE2="x",AE1≠""),AE1&", ","")

fill right with that formula to AJ3

AK3 =IF(CONCAT(AE3:AJ3)="","",TEXTBEFORE(CONCAT(AE3:AJ3),", ",−1))

To finish it off, the formula in AK2 would be =AK3 and you would hide row 3.

You might consider using a checkbox instead of typing an X. The result of a checkbox is either TRUE or FALSE. The IF statements would change from IF(AND("AE2="x",AE1<>""),AE1&", ","") to =IF(AND(AE2,AE1<>""),AE1&", ","")

It ignores completely empty cells but not cells that have functions or anything else in them. I think it should ignore "", but it does not. Interestingly, though, it will ignore cells that have "" until it hits a cell that has something else in it. After that, it quits ignoring "". I think it is a bug and I reported it as one a year ago. Nothing has been done to fix it.

With some more functions you can strip off the excess commas.

=SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ",TRUE,C),REGEX("(, )+"),", "),REGEX(", $"),"")

The inner SUBSTITUTE replaces all single and repeating ", " with a single ", ". The outer one eliminates any trailing ", " at the end of the string.

Folbo,

I can't add anything regarding how TEXTJOIN operates, but I can offer an alternate solution that is pretty straightforward. There are six hidden columns, but all the expressions and functions used are low difficulty.

There are four separate tables, so you can rearrange their positions if that suits your view layout better.

There are no formulas of any kind in the All Stores table. Just your data.

The second, third and fourth tables are identical, with the exception of the size input in cell A1. With this design you can easily add additional sizes if you wish.

Here's one of the breakout tables:

Best regards,

Jerry

I am not able to duplicate your problem from the information provided. Post a screenshot of your table wit the results you are getting and the formula you are using and also provide the results you expect/want. If you are using a localization and language other than US and English, let us know that, too. Sometimes it matters.

Interesting to use substitute, but i found some problem when i want to ended a sentence with “.” It will return error.

also when text join start to give “, “ on the very first join sentence, how can ride off this first “, “

please help, i just move to number. Everything looks easy in excel because have array, now i must type a single formula to achieve same.

many thanks before

Sorry for my bad english, im from Indonesia.

I'm worried if my formula looks complicated because I can't find the right substitute to input an array model like in excel. The idea is when i type "x" on blank row cell below, caption on the top line will appear on collumn AK with join text.

This is what I get when using your recommended formula to get rid of ", "

it works perfect, but how about ", " before sentences?

Please help provide formula to get rid ", " before sentences. the result should be:

Bab Kedua, Bab ketiga.

thanks before friend..

Truly masterpiece "Badunit"

That's what I was expecting for, even though I have to rewrite formulas that are so long compared to my excel but that's what learning is all about.

A little addition so that the part that is not checked goes to the next breakline. The checkbox idea is very inspiring

may God bless you