CODEWITHSUNDEEP
excelvba
Jimbo
Jimbo

Reputation: 1

Count number of sentences in a given cell

I have tried 2 different formulas the first:

 TRIM(MID(SUBSTITUTE($C2,"#",REPT(" ",LEN($C2))),(LEN($C2)*(COLUMN(C2)-3))+1,LEN($C2)))

I have also tried STR_SPLIT($C2,"#",COLUMN(C2)) with the VBA code

Function STR_SPLIT(str, sep, n) As String

    Dim V() As String
    V = Split(str, sep)
    STR_SPLIT = V(n - 1)    
    
End Function

My issue is - because there are different numbers of sentences in the cells I have to keep changing the formula to capture all the sentences.

For example:

and so on.

I did get around this by changing the -3 number in part of the formula (COLUMN(C2)-3)) 13 to -4 and -5 and so on. The numbers quoted are the maximum number of sentences possible in the different cells. However cell C3 only has 4 sentences in it. I can't work out how to change the macro so it covers all the different counts of sentences.

The words of a given sentence will always be the same. There are 29 columns of sentence data and over 2000 lines My aim is to count how many sentences there are and then how many of each so I can put them in order of occurrence can any one help please Attached is a picture example of the data.

Image showing sample of the data

Upvotes: 0

Views: 602

Answers (1)

Zev Spitz
Zev Spitz

Reputation: 15377

You can do this with a trivial VBA function:

Function NumberOfSentences(s As String) As Integer
NumberOfSentences = UBound(Split(s, "#")) + 1
End Function

and then call it from a cell with:

=NumberOfSentences(C2)

Upvotes: 1

Related Questions