Combining multiple cell text with punctuation

[Guest]

I would like to combine text from multiple cells inserting a comma between each word and the word "and" before the last cell combined, followed by a period.
NO problem you say, Just use the concatentate function. Here's the problem.
Cells A1:A15 are cells that when checked will add a new word to the sentence.
For instance, I put an X in A1, this adds the word found in cell B1 to my growing sentence. This should continue thru each cell in column A1:A15.
There could be as little as one cell chosen inwhich case no "and" should be added but should be followed by a period.
Thanks for the help.

 

[Jason Morin]

Put this in C1 and fill down to C15:

=IF(AND(COUNTIF(A1:$A$15,"X")=1,A1="X"),"and "&B1&".",IF
(A1="X",B1&",",""))

and now use your concatenate formula:

=C1&C2&C3&C4&C5&C6&C7&C8&C9&C10&C11&C12&C13&C14&C15

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to combine text from multiple cells

inserting a comma between each word and the word "and"
before the last cell combined, followed by a period.

NO problem you say, Just use the concatentate function. Here's the problem.
Cells A1:A15 are cells that when checked will add a new word to the sentence.
For instance, I put an X in A1, this adds the word found

in cell B1 to my growing sentence. This should continue
thru each cell in column A1:A15.

There could be as little as one cell chosen inwhich case

no "and" should be added but should be followed by a
period.