Need macro or function to count words written in UPPERCASE

[OhioRuss]

Hi,
I would like to have a macro or function that will count all the words (length >= 3) written in uppercase within a selection (such as column A). The cells contain multi-word text content, such as "The veterans on the team were SMITH, JONES, and B. HALLIDAY." That example would count as three uppercase words towards the grand total.

Thanks in advance!

Russ

[jindon]

UDF
Use in cell like
=CountUWord(A1)

Please Login or Register  to view this content.

[Rick Rothstein]

Here is a non-RegExp UDF (user defined function) that you can also consider...

Please Login or Register  to view this content.

[OhioRuss]

Thank you both, jindon and Rick. I can get both methods to work, but only if the argument of the function is a single cell (countuword(al) or wordcountuc(a1)). When I try to make the argument a range (a:a, or a1:d20, for example), I get a #VALUE error. Is there a tweak to fix that? I need to be able to count a range.

Russ

[jindon]

e.g.
=CountUWord(A1:A8)

Please Login or Register  to view this content.

[OhioRuss]

Thanks again, Jindon. That does the trick. I will mark the thread solved.

Russ

[Rick Rothstein]

For the record, here if my function modified to handle ranges...

Please Login or Register  to view this content.

NOTE: There is one difference between jindon's function and mine... my function can handle non-contiguous ranges. So, if you wanted to count the upper case words in, say, cells H1, H2, J3 and K3 you would call the function like this...

=WordCountUC((H1:H2,J3:K3))

Note the "extra" parentheses surrounding the comma delimited non-contiguous ranges (you could include additional ranges in the comma delimited list as needed). Of course, if you need the functionality, I am sure jindon can modify his function to accommodate such a need.

[OhioRuss]

Rick, thanks, that's helpful. Does that have anything to do with the RegExp difference you mentioned? I have no idea what that refers to.

Russ

[Rick Rothstein]

Rick, thanks, that's helpful. Does that have anything to do with the RegExp difference you mentioned? I have no idea what that refers to.

I am a little bit unclear about your question, but let me take a guess at at response. Contiguous ranges are rectangular groups of cells (for example, A3:A10 or B5:M5 or C3:F9)... non-contiguous ranges are two or more contiguous ranges (for example C3:F9 and M10:R20). As written, jindon's function needs the argument passed into it to be a single contiguous range whereas my function knows how to handle non-contiguous ranges. The note I provided explains how you have to specify non-contiguous ranges to my function (the addresses for the contiguous ranges making up the non-contiguous range must be delimited by commas and that comma-delimited list must be surrounded by their own set of parentheses (independent of the parentheses required by the function call itself). Did I guess correctly as to what you were inquiring about?

[jindon]

OhioRuss,

Some one is concerinig about what you don't even think of...

This is to allow multi type of data source.

You can use like
=CountUWord(A1,D1:D2,"ABCD Efg",D6:D7,{"Axv","BBC 456"})

Please Login or Register  to view this content.