How to count words between first and last comma?

[Okoth]

I'm looking for a way to count the words between the first and last comma. The sentence in the cell is dynamic. An example is this

Get a pan ready. Add milk, eggs, pepper, salt, half the sugar, onions and ham.

I can imagine the easiest way of doing it is to count the total number of words in the sentence(s) and deduct the number of words before the first comma and words after the last comma from it, but I don't know how to do it.

Please help

Thanks

 

[maras]

For your example, what result do you expect? Enter the result.

 

[Okoth]

In the case of this sentence: "Get a pan ready. Add milk, eggs, pepper, salt, half the sugar, onions and ham." the result should be 6, because that is the number of words between the first and the last comma.

 

[Sanjeev1976]

In the case of this sentence: "Get a pan ready. Add milk, eggs, pepper, salt, half the sugar, onions and ham." the result should be 6, because that is the number of words between the first and the last comma.

If your sentence is in cell A2 try the following. The formula is little bit long, but does what is required by you.

Book3
	B
2	6
Sheet1

 

[Amit Tandon]

Number of words (each divided by a space), between 1st and last comma:

=LEN(TRIM(MID(LEFT(A2,LOOKUP(1E+100,FIND(",",A2,ROW(INDIRECT("1:"&LEN(A2)))))-1),SEARCH(",",A2)+1,LEN(A2))))-LEN(SUBSTITUTE(TRIM(MID(LEFT(A2,LOOKUP(1E+100,FIND(",",A2,ROW(INDIRECT("1:"&LEN(A2)))))-1),SEARCH(",",A2)+1,LEN(A2)))," ",""))+1

We can avoid using the volatile INDIRECT function by using ROW(1:255) instead also presuming the length of cell A2 string would be within this limit.

 

[Okoth]

Amazing how you guys do these things. No wonder I couldn't figure this out. It was even more complicated than I thought.

Thank you very much!