Textjoin function to create a comma separated list with "and"

[excel151515]

Long time reader, first time poster. This is the first time I have not been able to find a solution to my problem.

My goal is to get excel to join a series of texts into a sentence that has commas between each text string, with "and" between the last and second last string. I can create the comma separated list using the textjoin function, but cannot figure out how to insert an "and" prior to the last string.

By way of example, I have 3-6 points of criteria, for example colours, that may apply to a specific product. For "Product A", where "blue" "red" and orange" apply, I want a cell which states "blue, red, and orange".

Any help is greatly appreciated!

 

[excel151515]

Gentlemen, again, this is awesome so thank you very much. The last version that gets rid of the help column does not seem to work. It's not working because the "blank" cells are blank because of an IF statement that results in "", as opposed to being truly blank.

Is there a way to remove the comma before and if there is only 2 results?

Thanks again!

 

[KRice]

Are all of the "blanks" generated by IF statements that insert a null (""), or are only some blanks caused by this?

 

[KRice]

Try this. The issue is the COUNTA function. I replaced it with SUMPRODUCT to determne how many cells were not occupied by a "".

mrexcel_20200416.xlsm
	I	J	K	L	M	N	O	P
23		blue	red	orange	yellow	purple		blue, red, orange, yellow, and purple
24		blue		orange	yellow	purple		blue, orange, yellow, and purple
25		blue			yellow			blue and yellow
26			red					red
27	blanks due to IF ->	here	there					here and there
excel151515

Cell Formulas
Range	Formula
J27:K27	J27	=IF(F$12=1,F11,"")
L27:N27	L27	=IF(H$28=1,H27,"")
P23:P27	P23	=SUBSTITUTE(TEXTJOIN(IF(SUMPRODUCT(--(J23:N23<>""))=2," and ",", "),TRUE,J23:N23),",",", and",MAX(1,COUNTA(J23:N23)-1))

 

[KRice]

The COUNTA function that was left in the previous post can lead to some odd results, but I haven't encountered any examples where that becomes a problem. This version might be a little better...this replaces both COUNTA functions:

=SUBSTITUTE(TEXTJOIN(IF(SUMPRODUCT(--(J27:N27<>""))=2," and ",", "),TRUE,J27:N27),",",", and",MAX(1,SUMPRODUCT(--(J27:N27<>""))-1))

 

[excel151515]

Gentlemen, this has officially solved my issues. I can now move on to other parts of my workbook to find some automation to employ!

Thank you both again.

 

[KRice]

Great news! You're welcome...we're happy to help. This problem reminded me about the different meanings of "nothing."