Count words between two characters

[Okoth]

What I'm trying to achieve is that if there is a text in A1 like this

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus ut lectus est. Pellentesque cursus ut ipsum sed commodo. Mauris ullamcorper, odio ultricies scelerisque dictum, quam mauris dapibus justo, sit amet ornare est urna at risus. Quisque sodales euismod nunc a vulputate.

I want to put in B1 a message that says "Sentence too long" if one or more of the sentences in A1 has more than 15 words.

Is it possible to count the number of words between multiple periods in a cell?

 

[Michael M]

I'm guessing there will be a much better way, but try

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))>15,"Sentence too long",LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

 

[Okoth]

I'm afraid that won't do the job, Michael. If there is one sentence in the cell that is longer than 15 words I want to "Sentence too long". But if all are shorter, B1 stays empty.

 

[Michael M]

Maybe this

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))>15,"Sentence too long","")

But is there always going to be more than one sentence in a cell ??

 

[Okoth]

Good question. There isn't. I am searching for a formula that checks if there is one.

 

[Okoth]

But there are always more than one sentence in the cell.

 

[Okoth]

Thinking of just using helper cells. The maximum number of sentences is about 6 or 7 so it is doable.

 

[Peter_SSs]

If you are happy to use vba, you could try this user-defined function.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down. If any sentences are too long, the function reports which numbered sentence(s) are too long and how many word are in them.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function CheckSentences(s As String) As String
  Dim aSentences As Variant, aWords As Variant
  Dim msg As String
  Dim i As Long
  
  Const MaxLength As Long = 15 '<- Maximum words allowed in a sentence
  
  aSentences = Split(Replace(Application.Trim(Replace(Replace(s, "?", "."), "!", ".")), ". ", "."), ".")
  For i = 0 To UBound(aSentences)
    aWords = Split(aSentences(i))
    If UBound(aWords) >= MaxLength Then
      msg = msg & ", " & i + 1 & "(" & UBound(aWords) + 1 & ")"
    End If
  Next i
  If Len(msg) = 0 Then
    CheckSentences = "All Ok"
  Else
    CheckSentences = "These sentences are too long: " & Mid(msg, 3)
  End If
End Function

Excel 2016 (Windows) 32 bit
	A	B
1	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus ut lectus est. Pellentesque cursus ut ipsum sed commodo. Mauris ullamcorper, odio ultricies scelerisque dictum, quam mauris dapibus justo, sit amet ornare est urna at risus. Quisque sodales euismod nunc a vulputate.	These sentences are too long: 4(17)
2		All Ok
3	This sentence needs to have many, many, many words in it to be too long for this test. The sentence is short. The first sentence in this cell had more than the allowed number of words and so does this one.	These sentences are too long: 1(18), 3(19)
4	The cat sat on the mat. The mat was in front of the fire.	All Ok
Sheet1

Cell Formulas
Range	Formula
B1	=CheckSentences(A1)

 

[Okoth]

Thank you for the vba. I'm using Excel 2016 64 bit. I'm getting a #NAME? error: Ambiguous name detected.

This happens when I paste =CheckSentences(A1) into B1.

I see a slight difference with your explanation.
"3. Copy and Paste the code below into the main right hand pane". I see only one screen.

The file is saved as .xlsm. No idea how to get rid of the error. Hope this rings a bell for you...

 

[Okoth]

Please don't reply yet, because I might have it working. I used you code on a different sheet as well and that might have caused it. I'll let you know later.