Automatically punctuate a formula that lists text with commas and, fullstop.

I was very kindly helped with some formulas a few weeks ago to help me query a summary spreadsheet, I didn't finish the job then, but am trying to now. What I am trying to do is create a written report with the data coming from summary sheet queries. This will help me reduce user error cutting and pasting data.

What I would like to do is for the formula in k20 to return the text from each cell with a comma between each except for the last for which I would like an "and" before and a full stop after. That is make the text grammatically correct. So instead of k20 reading:

Test had a definite difference more than others for C. Vestibular Processing and E. Multisensory Processing and F. Oral Sensory Processing.

I would like it to read:

Test had a definite difference more than others for C. Vestibular Processing, E. Multisensory Processing and F. Oral Sensory Processing.

Is this possible?

Thanks for your input, I appreciate you taking the time to look at this. Unfortunately, it doesn't help with my real problem which is getting the " and" and a "." before and after the last cell with data. I can get a comma in each cell by changing the formulas from cells k5 down without using the substitute function, but that leaves me exactly the same issue as now which is finishing the sentence correctly with an and and a full stop.

Many thanks for this, but probably down to my error it doesn't work. I've copied the formula into cell K5. Copied it again and pasted into K6 to K18. Hit F9 and cells K% to K18 are blank and K19 and K20 stay the same. I get an error message saying that there are circular references. I'm using excel 2013 if that makes any difference. I tried changing the L's to K's, but that didn't work either. It's late here, so I'll look up the functions you've used in the morning and see what I'm doing wrong. If this works it's going to be great because I can use it for each summary column to reduce the summary to five lines of text.

Just had another look at this and managed to work out that I need to turn on the iterative calculations option for this formula to work and return a result in k5 to k18. This returns the correct row labels, but they all have ", " after them. The last one does not have the "and " before and "." after. The calculated cell K19 is therefore the same as with the existing formulas, but with a ", "replacing the "and".

I'm trying to work out how this works, but don't know what the -- operator does, or why there are references to column L. Also as the formula references an adjoining column does that mean I will not be able to use column L for a similar formula that queries column J? I am trying to get to grips with this, but I am trying to get my head around how the following formulas types work

IF(SUMPRODUCT(--(OFFSET(K4:K$5,1,0)<>""))+1=SUMPRODUCT(--($L$5:$L$18<>""))

Do I need to start somewhere more basic to understand this? What I intend to do is use this type of formula to summarize columns E to I for the three sheets on the summaries tab.

Sorry to be asking again, but this is all very new to me. I'm just looking at this again and trying to work out what the formula here is doing and why it is not working. Am I correct in assuming the following:

The -- operator makes sure that I get a numerical answer to the offset function
The Sumproduct and Offset functions together are counting the cells with data so that you can check if it is the last cell in the column with data (that is why the if statement has a +1)

What I don't understand is why the formula references an empty column. If any one could explain this I would appreciate it as I do want to learn how troubleshoot these functions when I get better with excel.

Thank you so much for that Richard, I really appreciate the help. This is going to save so much time and reduce human error, because previously this was all done by hand and then typed up. So this works by checking if the cell is the last one by counting incrementally downwards through the results. That is really clever and I'm not sure that with my present skill set that I could come up with something like that. I have learnt a huge amount today though and it may encourage me to have a go at one of the more complex tests that we use.

Thanks again