Delimiter Dilemma

[DeExcelerated]

Hi All,

How do I add an "Oxford" comma?

From the example below, the formula =SUBSTITUTE(REPLACE(REPT(", "&A2,A2<>"")&REPT(", "&B2,B2<>"")&REPT(", "&C2,C2<>"")&REPT(", "&D2,D2<>"")&REPT(", "&E2,E2<>"")&REPT(", "&F2,F2<>""),1,2,""),", "," and ",MAX(1,COUNTIF($A2:$F2,"?*")-1)) works but does not add a comma before the "and" delimiter on two or more values (as shown below):

Huggy Bear = Huggy Bear
Huggy Bear Mamma Bear = Huggy Bear and Mamma Bear
Huggy Bear Mamma Bear Brother Bear = Huggy Bear, Mamma Bear, and Brother Bear
Huggy Bear Mamma Bear Brother Bear Sister Bear = Huggy Bear, Mamma Bear, Brother Bear, and Sister Bear
Huggy Bear Mamma Bear Brother Bear Sister Bear Errant Bear = Huggy Bear, Mamma Bear, Brother Bear, Sister Bear, and Errant Bear
Huggy Bear Mamma Bear Brother Bear Sister Bear Errant Bear Baby Bear = Huggy Bear, Mamma Bear, Brother Bear, Sister Bear, Errant Bear, and Baby Bear

Any suggestions?

 

[mole999]

=SUBSTITUTE(REPLACE(REPT(", "&A2,A2<>"")&REPT(", "&B2,B2<>"")&REPT(", "&C2,C2<>"")&REPT(", "&D2,D2<>"")&REPT(", "&E2,E2<>"")&REPT(", "&F2,F2<>""),1,2,""),", ",", and ",MAX(1,COUNTIF($A2:$F2,"?*")-1)) maybe?

 

[DeExcelerated]

=SUBSTITUTE(REPLACE(REPT(", "&A2,A2<>"")&REPT(", "&B2,B2<>"")&REPT(", "&C2,C2<>"")&REPT(", "&D2,D2<>"")&REPT(", "&E2,E2<>"")&REPT(", "&F2,F2<>""),1,2,""),", ",", and ",MAX(1,COUNTIF($A2:$F2,"?*")-1)) maybe?

OMG. It's that simple? I've spent the last day and a half rearranging the COUNTIF part of the formula thinking it was the placement of the "and" delimiter and not just adding another "," at the end of the first string.

Oh, woe is me and my making a formula more convoluted than it needed to be.

THANK YOU

 

[DeExcelerated]

WAIT!!

The comma delimiter can't be placed on two values only; it has to be three or more values:

Huggy Bear and Mamma Bear
Huggy Bear, Mamma Bear, and Brother Bear

The formula produces:

Huggy Bear, and Mamma Bear

Which is not considered an "Oxford" comma...

Sigh...

 

[mole999]

you might add a further replace and find the very first one and delete that ?

 

[DeExcelerated]

you might add a further replace and find the very first one and delete that ?

Thanks for the suggestion. I wasn't able to make it work. I'll keep at it.

 

[jtakw]

Hi,

Try it this way:

Book1
	A	B	C	D	E	F	G
2	Huggy Bear						Huggy Bear
3	Huggy Bear	Mamma Bear					Huggy Bear and Mamma Bear
4	Huggy Bear	Mamma Bear	Brother Bear				Huggy Bear, Mamma Bear, and Brother Bear
5	Huggy Bear	Mamma Bear	Brother Bear	Sister Bear			Huggy Bear, Mamma Bear, Brother Bear, and Sister Bear
6	Huggy Bear	Mamma Bear	Brother Bear	Sister Bear	Errant Bear		Huggy Bear, Mamma Bear, Brother Bear, Sister Bear, and Errant Bear
7	Huggy Bear	Mamma Bear	Brother Bear	Sister Bear	Errant Bear	Baby Bear	Huggy Bear, Mamma Bear, Brother Bear, Sister Bear, Errant Bear, and Baby Bear
Sheet648

Cell Formulas
Range	Formula
G2	=SUBSTITUTE(REPLACE(REPT(", "&A2,A2<>"")&REPT(", "&B2,B2<>"")&REPT(", "&C2,C2<>"")&REPT(", "&D2,D2<>"")&REPT(", "&E2,E2<>"")&REPT(", "&F2,F2<>""),1,2,""),", ",IF(COUNTA(A2:F2)<3," and ",", and "),MAX(1,COUNTA(A2:F2)-1))

 

[DeExcelerated]

AWESOME!!

 

[jtakw]

Thanks for the feedback, welcome to the forum.