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:
[TABLE="width: 560"]
<tbody>[TR]
[TD]Contact 1[/TD]
[TD]Contact Title 1[/TD]
[TD]Contact 2[/TD]
[TD]Contact Title 2[/TD]
[TD]Contact 3[/TD]
[TD]Contact Title 3[/TD]
[TD]Contact 4[/TD]
[TD]Contact Title 4[/TD]
[TD]Contact 5[/TD]
[TD]Contact Title 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]coo[/TD]
[TD][/TD]
[TD]cio[/TD]
[TD][/TD]
[TD]pmo[/TD]
[TD][/TD]
[TD]ciso[/TD]
[TD][/TD]
[TD]ceo[/TD]
[/TR]
</tbody>[/TABLE]

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
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
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>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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))