I have some text in a cell (let's say A1) in Google Sheets and wish to count how many words have initial capital letters. I've tried a couple of things, but neither works:

=countif(split(A1," "),regexmatch(split(A1," "),"^\W*[A-Z]"))

=sum(arrayformula(regexmatch(text(split(A1," "),0),"^\W*[A-Z]")))

What should I be doing, please?

A couple of notes:

  • I want to do this for multiple cells, i.e. to know how many words in A1 are capitalized, how many in A2 are, etc. I doubt that that will affect the answer, but am mentioning it just in case.
  • The regular expression I used above is not a good way to check for capitalization in general, and don't use it. (See e.g. the "brokenness" section of Tom Christiansen's "Go Thou and Do Likewise" post on Stack Overflow. It's about Perl, but much of it generalizes.) It happens to work for my data, because my data uses a specific set of characters that it happens to work for. You have been warned.

Here's what I wound up doing (though I would still love to see an in-place solution, by which I mean one that doesn't require the use of additional cells):

I created two additional tabs (worksheets). In the first I put =split(A!A1," "), which yielded a row of words, one per cell. I called that worksheet "split". In the second I put =if(not(regexmatch(text(split!A1,0),"^\W*[A-Za-z]")),,regexmatch(text(split!A1,0),"^\W*[A-Z]")) and copied it across the row to get the truth value in each cell of whether the word has an initial capital letter. Then I added column A to the left containing =if(counta(B1:Z1)=0,,countif(B1:Z1,true)).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.