Commas and and symbol insertion

[rzarcta]

I have some data in columns that is separated by commas:

person1, person 2, person3, person4, person5
person6, person7, person8
person44, person9, person10, person 99
etc.

I want to be able to insert an "and" after the last comma so it would read:

person1, person 2, person3, person4, and person5
person6, person7, and person8
person44, person9, person10, and person 99

I used text to columns and then sorted by the number of columns newly created and then inputted & statements with "," and "and" based on the number of entries each row had. it worked but it was quite tedious considering I have thousands of rows.

Is there an easier way to do this?

 

[Michael M]

Try

=SUBSTITUTE(A1,", "," and ",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

 

[mikerickson]

This is without the Oxford comma
=SUBSTITUTE(A1,",", " and", LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

This is with
=SUBSTITUTE(A1,",", ", and", LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

 

[rzarcta]

This is without the Oxford comma
=SUBSTITUTE(A1,",", " and", LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

This is with
=SUBSTITUTE(A1,",", ", and", LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

Thanks! This works amazingly well! You are a life saver...