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!

 

[KRice]

Welcome to the MrExcel board. This is messy, but here is one way. There should be a convenient option for the Oxford comma:

mrexcel_20200416.xlsm
	J	K	L	M	N
1
2	blue	red	orange		blue, red, and orange
Sheet23

Cell Formulas
Range	Formula
N2	N2	=SUBSTITUTE(TEXTJOIN(", ",TRUE,J2,K2,L2),",",", and",LEN(TEXTJOIN(", ",TRUE,J2,K2,L2))-LEN(SUBSTITUTE(TEXTJOIN(", ",TRUE,J2,K2,L2),",","")))

 

[excel151515]

Welcome to the MrExcel board. This is messy, but here is one way. There should be a convenient option for the Oxford comma:

mrexcel_20200416.xlsm
	J	K	L	M	N
1
2	blue	red	orange		blue, red, and orange
Sheet23

Cell Formulas
Range	Formula
N2	N2	=SUBSTITUTE(TEXTJOIN(", ",TRUE,J2,K2,L2),",",", and",LEN(TEXTJOIN(", ",TRUE,J2,K2,L2))-LEN(SUBSTITUTE(TEXTJOIN(", ",TRUE,J2,K2,L2),",","")))

KRice this is fantastic thank you. I have 2 follow up questions.
1. How would I go about adding categories, I actually have 7 in total.
2. How can this be modified so that if only 1 category applies, it simply states the one category ie. "blue.". Currently, if only 1 applies it gives a value of #VALUE!

I can't tell you how many hours I've spent trying to solve this so I very much appreciate it.

 

[KRice]

Additional categories are easily added by adding onto the concatenation string, but that would have to be done in three places in that single formula. I see two issues...you mentioned one...the other involves just two categories, where you probably do not want the comma. For example, should it read as "blue and yellow"?

I'll post an idea for resolving this.

 

[KRice]

Unfortunately, these exceptions require expanding the formula so that there are now 5 sets of TEXTJOIN functions that need to be updated. If you are not opposed to a helper cell (which could be hidden), the TEXTJOIN could be moved to a different column, and then this function would simply incorporate a reference to the cell containing the TEXTJOIN formula...and then there would be only one TEXTJOIN function to update with additonal categories...see the lower block in the sheet as an example.

mrexcel_20200416.xlsm
	I	J	K	L	M	N	O	P	Q
1
2		blue	red	orange	yellow	purple		blue, red, orange, yellow, and purple
3		blue		orange	yellow	purple		blue, orange, yellow, and purple
4		blue			yellow			blue and yellow
5		blue						blue
6	all blanks ->
7								Results	helper column (hide)
8		blue	red	orange	yellow	purple		blue, red, orange, yellow, and purple	blue, red, orange, yellow, purple
9		blue		orange	yellow	purple		blue, orange, yellow, and purple	blue, orange, yellow, purple
10		blue			yellow			blue and yellow	blue and yellow
11			red					red	red
12	all blanks ->
Sheet23

Cell Formulas
Range	Formula
P2:P6	P2	=IF(COUNTA(J2:N2)=2,TEXTJOIN(" and ",TRUE,J2,K2,L2,M2,N2),IFERROR(SUBSTITUTE(TEXTJOIN(", ",TRUE,J2,K2,L2,M2,N2),",",", and",LEN(TEXTJOIN(", ",TRUE,J2,K2,L2,M2,N2))-LEN(SUBSTITUTE(TEXTJOIN(", ",TRUE,J2,K2,L2,M2,N2),",",""))),TEXTJOIN(", ",TRUE,J2,K2,L2,M2,N2)))
P8:P12	P8	=IFERROR(SUBSTITUTE(Q8,",",", and",LEN(Q8)-LEN(SUBSTITUTE(Q8,",",""))),Q8)
Q8:Q12	Q8	=TEXTJOIN(IF(COUNTA(J8:N8)=2," and ",", "),TRUE,J8,K8,L8,M8,N8)

 

[Eric W]

How about:

Book1
	I	J	K	L	M	N	O	P
2		blue	red	orange	yellow	purple		blue, red, orange, yellow and purple
3		blue		orange	yellow	purple		blue, orange, yellow and purple
4		blue			yellow			blue and yellow
5		blue						blue
6	all blanks ->
Sheet1

Cell Formulas
Range	Formula
P2:P6	P2	=SUBSTITUTE(TEXTJOIN(", ",TRUE,J2:N2),","," and",MAX(1,COUNTA(J2:N2)-1))

It lacks the Oxford comma, but makes up for it with a fairly simple formula. I guess it depends on your style book.

Edit: This has the Oxford comma:

=SUBSTITUTE(TEXTJOIN(", ",TRUE,J2:N2),",",IF(COUNTA(J2:N2)>2,", and"," and"),MAX(1,COUNTA(J2:N2)-1))

 

[KRice]

I like Eric's consolidation within the TEXTJOIN function. If all of the cells to join are contiguous, then this offers the best of both, I think...shorter formula and the Oxford comma...but it does use a helper cell.

mrexcel_20200416.xlsm
	I	J	K	L	M	N	O	P	Q
7								Results	helper column (hide)
8		blue	red	orange	yellow	purple		blue, red, orange, yellow, and purple	blue, red, orange, yellow, purple
excel151515

Cell Formulas
Range	Formula
P8	P8	=IFERROR(SUBSTITUTE(Q8,",",", and",LEN(Q8)-LEN(SUBSTITUTE(Q8,",",""))),Q8)
Q8	Q8	=TEXTJOIN(IF(COUNTA(J8:N8)=2," and ",", "),TRUE,J8:N8)

 

[Eric W]

If you look back at my post #6, I managed to edit it in time to add a version with the Oxford comma.

 

[KRice]

I used another feature in Eric's solution to further shorten the formula, and now it is practical to eliminate the helper cell and incorporate just one TEXTJOIN function in the formula. The range J23:N23 (3 places) needs to be adjusted to accommodate more categories.

mrexcel_20200416.xlsm
	I	J	K	L	M	N	O	P
22								Results
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	all blanks ->
excel151515

Cell Formulas
Range	Formula
P23:P27	P23	=SUBSTITUTE(TEXTJOIN(IF(COUNTA(J23:N23)=2," and ",", "),TRUE,J23:N23),",",", and",MAX(1,COUNTA(J23:N23)-1))

 

[KRice]

Perfect!...no, sorry, I missed that. Nice work!