Count words between two characters

Okoth

Okoth

Board Regular
Joined
Sep 10, 2009
Messages
106
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
I'm guessing there will be a much better way, but try

Code: 
=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))>15,"Sentence too long",LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)
 
Upvote 0
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.
 
Upvote 0
Maybe this

Code: 
=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 ??
 
Upvote 0
Good question. There isn't. I am searching for a formula that checks if there is one.
 
Upvote 0
But there are always more than one sentence in the cell.
 
Upvote 0
Thinking of just using helper cells. The maximum number of sentences is about 6 or 7 so it is doable.
 
Upvote 0
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)

Rich (BB code): 
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
AB
1Lorem 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)
2All Ok
3This 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)
4The cat sat on the mat. The mat was in front of the fire.All Ok
Sheet1
Cell Formulas
RangeFormula
B1=CheckSentences(A1)
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Pull names that left messages
Replies
3
Views
109
Fluff
Fluff
B
  • Question
XML to PDF
Replies
0
Views
119
BijanBorazjani
B
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
389
Peter_SSs
Peter_SSs
R
  • Question
How to find a range of words in a list of row?
Replies
8
Views
630
Eric W
Eric W
D
  • Question
Extract Latitude e Longitude strings from text
2
Replies
10
Views
2K
deltamu
D

Forum statistics

Threads
1,214,622
Messages
6,120,585
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top