Google-sheets – How many words in a string are capitalized

google sheetstext;

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.

Best Answer

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)).

Related Topic