Filter out all caps records

[ddye]

Hi, 

I have data where I am trying to remove the row if a spefic field is in all caps.  I thought I may be able to count lowercase characters, and filter out the zeros, but I am not sure why I am getting an incorrect result from my formula.  Any ideas?  Thank you.

Formula: REGEX_CountMatches([Task Name], "[\L]")

Task Name               Count

DEMOLITION              0

D.O.T. Approaches      4

Sidewalks                    0

Transformer Pad         1

[JohnJPS]

Can you do [A-Z] instead of [\L]?

[ddye]

Thanks John,

That doesn't seem to specifically count lowercase only characters.  It looks like it is just counting all letters.

I am not sure what [\L] is counting either though, but it is pretty close to what I am trying to do.

[Federica_FF · Accepted solution]

HI,

try with

REGEX_CountMatches([Column], "\u", 0)

\u means uppercase, 0 is needed if you want to activate the case sensitive.

[ddye]

Looks like it has something to do with how many seperate words there are in the field.  One word records are getting a count of 0, regardless of how many lowercase characters there are in the word.  

[JohnJPS]

Confirmed here... wow... that is weird.

Anyway, this seems to work:

Contains(UPPERCASE([f1]),[f1],0)

... where [f1] is the field in question.

A-ha, and @Federica_FF spotted the case sensitivity flag on the REGEX functions too!  Good eye. 

[ddye]

Thanks!  That works, and is getting me what I need.  Really appreciate it.

I am a bit confused though as to why I would get a 0 result on CARPENTRY, which is what it is giving me.  

[BenMoss]

Here is a slightly different method to that outlined in the above answers (probably seen as less efficient also) but it works none the less and essentially expands from an answer given yesterday.

You create a line for each of the individual characters within the string and use to chartoint() formula to return the numeric code for that character. From this we can flag characters that are capitals (capital letters return a value between 60 and 95, as outlined in this table http://www.asciitable.com/).

We can then create a count of the uppercase flag.

[ddye]

That is really cool.  I am about to start working with some pretty nasty data, so I may try to incorporate this in order to filter out junk unicode i know it is going to have in it. 

Thanks!

[alfredcsy]