I have a column with a lot of rows more than 150k, each cell has a text, there is some cells has problems of having some sentences with capital letters I wanna fix this issue how can I filter them to know how many of them I have for example I have some cells like that I wanna detect the cells that has some capital sentences to be able to fix them:

Each year, They carefully curate the finest gifts to fill our Baskits from new and wellloved brands, to unique products made exclusively for us. They specialize in helping busy professionals give thoughtful, impactful gifts for business development, colleague/employee recognition, holiday gifts and more. Here are the top three things to know about us: 1) They ARE CANADA'S LEADING GIFT DELIVERY SERVICE 30 years of experience and 20, 000 customers 2) They MAKE THOUGHTFUL GIFTING QUICK AND EASY Online and Mobile Webstore (Open 24/7) Call Centre Gift Specialists Two Retail Stores (Downtown North Toronto) 3) They HAVE DELIVERY OPTIONS TO SUIT YOUR NEEDS Delivery Across North America sameday (and Saturday) Delivery in the GTA

CodePudding user response：

Since it looks like you're in Google Sheets, just do a REGEX_MATCH() for two capital letters in a row (as a review flag):

=BYROW(A2:A, LAMBDA(x, REGEXMATCH(x, "(.*)[A-Z]{2}(.*)"))

The BYROW() makes it a one-liner for the entire column. Ditch that if needed.

CodePudding user response：

try in A2:

=INDEX(REGEXMATCH(A2:A; "(.*)[A-Z]{2}(.*)")

or if you want a count:

=SUMPRODUCT(1*REGEXMATCH(A2:A; "(.*)[A-Z]{2}(.*)"))