0

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:

  • cell C2 has 7 sentences in it separated by #
  • cell D2 has 4 sentences in it separated by #
  • cell E2 has 5 sentences in it separated by #

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

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
Jimbo
  • 1

1 Answers1

1

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)
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • Hello thank you so much for your answer, this does work and it counts the number of sentences in each cell which I need to know. What I also need to know is how many times a particular sentence appears in all my data / across all cells. Example how many times does "Assessment notes not clear" or "Tone of Voice" appear in all my data. The idea is I can then rank them using a pivot table and the top 5 would be used to drive improvement in teams thank you – Jimbo Feb 25 '21 at 22:47
  • @Jimbo First, if this answer has solved your problem, indicate this by clicking on the check mark next to the answer. If the answer is useful, indicate this by upvoting (the up arrow next to the answer) (and of course, if the answer is not useful, indicate that by downvoting). Second, what you're asking here is really a new quesiton, and you should ask it as such. As to the actual problem, I would write code that goes over each cell, splits the cell contents into sentences as above, and uses a Scripting.Dictionary to store a count of each sentence. – Zev Spitz Feb 26 '21 at 06:28
  • 1
    @Jimbo An even better solution would be to replace the Dictionary with a [disconnected recordset](https://www.4guysfromrolla.com/webtech/080101-1.shtml); it's trivial to sort a recordset, while getting sorted data from a dictionary is not simple. – Zev Spitz Feb 26 '21 at 06:47
  • @Jimbo I've taken the liberty of posting this [as a new question](https://stackoverflow.com/q/66417649/111794) together with an answer. – Zev Spitz Mar 01 '21 at 07:16