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]

@PeterSS
I see the Like function has disappeared...which is probably a good thing....but I really did like your snippet of code. A very nice solution indeed !!!...ray:

 

[Peter_SSs]

I'm getting a #NAME? error: Ambiguous name detected.

That would indicate that you have two (or more) vba functions in the workbook with the name 'CheckSentences'

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.

In the vba window (where the code is) in the 'View' menu at the top, click on 'Project Explorer'. That should open another pane in the vba window (usually at the left) where you can see the various projects (workbooks) that you have open and their various Worksheets, Modules etc. Then the one with the actual code is usually to the right of that Project Explorer window pane.

@PeterSS
I see the Like function has disappeared...which is probably a good thing....but I really did like your snippet of code. A very nice solution indeed !!!...ray:

Thank you Michael.

 

[Rick Rothstein]

@PeterSS
I see the Like function has disappeared...which is probably a good thing

Why? I thought it was a great way to allow readers of the thread to show their appreciation for a post without clogging up the forum's servers with lots of "Way too go!" type messages.

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

One caveat with your UDF (user defined function)... it will be fooled by abbreviations such as Dr., Mr., Ph.d, etc. If the OP is okay with the function not identifying which sentence or sentences are too long, then this UDF could be used...

[table="width: 500"]
[tr]
	[td]Function CheckSentences(s As String) As String
  CheckSentences = Choose(2 + ("." & s & "." Like "[.?!]" & Application.Rept("*?* ", 15) & "*?*[.?!]"), "Too long", "All OK")
End Function
[/td]
[/tr]
[/table]

 

[Peter_SSs]

One caveat with your UDF (user defined function)... it will be fooled by abbreviations such as Dr., Mr., Ph.d, etc.

True, but I'm not sure how likely that is. Did you see the sample data?

BTW, yours is fooled by this.
"The cat sat on the mat. The dog ran away. The mouse squeaked. The horse came to look."

 

[Rick Rothstein]

BTW, yours is fooled by this.
"The cat sat on the mat. The dog ran away. The mouse squeaked. The horse came to look."

Interesting... I was just thinking about the function being fooled by those asterisks... I'll have to think about this some more.

 

[Michael M]

I agree with the comment

I thought it was a great way to allow readers of the thread to show their appreciation for a post without clogging up the forum's servers with lots of "Way too go!" type messages.

But sadly, just like where it probably originated (facebook), it gets overused and becomes a nuisance rather than a thank you !!
I believe a good old fashioned please and thank you show much more appreciation than ticking a like button....

 

[Peter_SSs]

I'll have to think about this some more.

I doubt that it is possible to deal completely with abbreviations, even if a comprehensive list of them was provided. Consider these two examples:

Mike watched while Dr. Smith treated his wife. (1 sentence)
The abbreviation for doctor is Dr. Smith is a noun used to describe a metal worker. (2 sentences)

 

[Okoth]

This is great and it is going to be helpful in so many of my sheets. Thank you very much!

 

[Rick Rothstein]

I doubt that it is possible to deal completely with abbreviations, even if a comprehensive list of them was provided. Consider these two examples:

Mike watched while Dr. Smith treated his wife. (1 sentence)
The abbreviation for doctor is Dr. Smith is a noun used to describe a metal worker. (2 sentences)

What a great example! Thanks.

 

[Peter_SSs]

This is great and it is going to be helpful in so many of my sheets. Thank you very much!

You are very welcome.