• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

Excel 2013 - Formula to count texts with capital letters

Dear Experts,

Could you please advise about an Excel formula, which counts how many cells are in a range with only capital letters?

In the attached example the result would be 3 (SOT,WBA,HUL)

Data
Thanks in advance,
0
csehz
Asked:
csehz
  • 4
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming the strings are in the range A1:A5, then try this.....

=SUMPRODUCT(--(EXACT(LEFT(A1:A5,1),UPPER(LEFT(A1:A5,1)))))

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think I misunderstood your question, I assumed you were trying to compare the first character of the words.

Please try this tweaked formula....

=SUMPRODUCT(--(EXACT((A1:A5),UPPER(A1:A5))))

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
And moreover if you want to ignore the numeric values and assuming the data is in the range A1:A6, please try this......

=SUMPRODUCT((ISTEXT(A1:A6))*(EXACT((A1:A6),UPPER(A1:A6))))
0
 
csehzAuthor Commented:
Thank you very much for your regular help, it works of course
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help this time also. :)
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now