Check for text in next cell, if none, add "and" to result

[babs43]

Happy Friday, all -

I have several columns which may contain text and I would like to add "and" before the last item.

Currently, I have

This	That	The other thing		This, That, The Other Thing
Tweedle Dum	Tweedle Dee			Tweedle Dum, Tweedle Dee

<tbody>
</tbody>

using the TextJoin join function. I would like to add "and" to the last item:

This	That	The other thing		This, That, and The Other Thing
Tweedle Dum	Tweedle Dee			Tweedle Dum and Tweedle Dee

<tbody>
</tbody>

Yes, I use the Oxford Comma I've tried nesting if statements, but I get stuck with "is blank" and so forth. Ideas?

 

[jproffer]

Is it always only 2 or 3 items or is there a chance for hundreds in one string? Thousands?
If it's only 2 or 3, If functions would probably be easiest. Maybe like this: (data in A1, A2, A3)

=If(A3="",A1 & " and " & A2, A1 & ", " & A2 & ", and " & A3)

I don't have excel at home to test it, but it should work.

 

[babs43]

Hi!! There is a max of 10.

 

[Eric W]

Welcome to the Board!

Maybe:

=SUBSTITUTE(TEXTJOIN(", ",TRUE,A2:J2),", ",", and ",COUNTA(A2:J2)-1)

as a basic version, and

=CHOOSE(SIGN(COUNTA(A2:J2)-2)+2,CONCAT(A2:J2),TEXTJOIN(" and ",TRUE,A2:J2),SUBSTITUTE(TEXTJOIN(", ",TRUE,A2:J2),", ",", and ",COUNTA(A2:J2)-1))

which handles the case of 0, 1, or 2 entries better.

 

[babs43]

Welcome to the Board!

Maybe:

=SUBSTITUTE(TEXTJOIN(", ",TRUE,A2:J2),", ",", and ",COUNTA(A2:J2)-1)

as a basic version, and

=CHOOSE(SIGN(COUNTA(A2:J2)-2)+2,CONCAT(A2:J2),TEXTJOIN(" and ",TRUE,A2:J2),SUBSTITUTE(TEXTJOIN(", ",TRUE,A2:J2),", ",", and ",COUNTA(A2:J2)-1))

which handles the case of 0, 1, or 2 entries better.

the CHOOSE (etc) function worked like a charm!!! thank you so very much!!!
Babs

 

[Eric W]

Happy to help!

 

[babs43]

Well, Mr. Eric W, I am asking for your help again! It is the same type of issue, except that instead of the cells being next to one another, they are separated, like so:

Contact 1	Contact Title 1	Contact 2	Contact Title 2	Contact 3	Contact Title 3	Contact 4	Contact Title 4	Contact 5	Contact Title 5
	coo		cio		pmo		ciso		ceo

<tbody>
</tbody>

and I would like to simply list the positions "coo, cio, pmo, ciso, and ceo"

What do you think?
Thank you in advance!!!
Babs

 

[Eric W]

Much trickier:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Contact 1	Contact Title 1	Contact 2	Contact Title 2	Contact 3	Contact Title 3	Contact 4	Contact Title 4	Contact 5	Contact Title 5
2		a	coo	a	cio	a	pmo	a	ciso	a	ceo		coo, cio, pmo, ciso, and ceo

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula M2 {=CHOOSE(SIGN(SUM(IF(ISODD(COLUMN(B2:K2)-COLUMN(B2)),IF(B2:K2<>"",1)))-2)+2,CONCAT(IF(ISODD(COLUMN(B2:K2)-COLUMN(B2)),B2:K2&"","")),TEXTJOIN(" and ",TRUE,IF(ISODD(COLUMN(B2:K2)-COLUMN(B2)),B2:K2&"","")),SUBSTITUTE(TEXTJOIN(", ",TRUE,IF(ISODD(COLUMN(B2:K2)-COLUMN(B2)),B2:K2&"","")),", ",", and ",SUM(IF(ISODD(COLUMN(B2:K2)-COLUMN(B2)),IF(B2:K2<>"",1)))-1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I keep thinking there's an easier way, but I can't think of what else I can remove or shorten. I'll cogitate a bit more and see if I can improve on that.

 

[Eric W]

If you're willing to live without the Oxford comma, this works:

=SUBSTITUTE(TEXTJOIN(", ",TRUE,IF(ISODD(COLUMN(B2:L2)-COLUMN(B2)),B2:L2&"","")),", "," and ",MAX(SUM(IF(ISODD(COLUMN(B2:L2)-COLUMN(B2)),IF(B2:L2<>"",1)))-1,1))

and it's over 200 characters shorter.

This gives you the Oxford Comma too and it's still shorter than the one in post #8 :

=SUBSTITUTE(TEXTJOIN(", ",TRUE,IF(ISODD(COLUMN(B2:L2)-COLUMN(B2)),B2:L2&"","")),", ",IF(SUM(IF(ISODD(COLUMN(B2:L2)-COLUMN(B2)),IF(B2:L2<>"",1)))>2,", and "," and "),MAX(SUM(IF(ISODD(COLUMN(B2:L2)-COLUMN(B2)),IF(B2:L2<>"",1)))-1,1))

 

[Eric W]

And using the same logic, the CHOOSE formula from post #4 can be replaced with:

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