Formula

Question

Hi All,

Is there any way in which if the cell contain any single letter (from a to z  or  A to Z) then in another cell i tag that as "Not Required" using formula.

example:

Column 1        Column 2

r                Not Required

ed_hayter · Answer

@RajatRehria Your request is a bit confusing but I've tried to rejig what I had - my understanding is you want a formula that checks for one occurrence of A-Z or a-z and if it finds one instance of this then mark as not required:

I opted for the following formula.

IIF(REGEX_CountMatches([Column 1], "[A-Za-z]") >= 1 AND Length([Column 1]) <= 2, "Not Required", "Required")

Two conditions if there is 1 or more instance of A-Z or a-z (now split into two ranges thanks to Philip Mannering's good point) and that the length of the column is 2 or less (to not match cases where the field is "aaa" for example.

Regex_match.yxmd

RajatRehria · Answer

Hi @ed_hayter many thanks for the solution, ur formular working goog with single character, however can u pls provide a formula where it mark one as well as two characters Not Required. Thanks.

philipmannering · Answer

You can just do,

IIF(REGEX_MATCH([Column 1], "[A-Z]"), "Not Required", "Required")

as it defaults to case insensitive. In fact if it were to add the icase=0 flag then [A-z] would be incorrect because it would include all the unicode characters between upper case A and lower case z which includes a few ascii characters and symbols like ', [, \, ], ^, _, and `