Validate data entry by testing if charachters are Alphanumeric

[masplin]

I'm still trying to find a way to test if an entry such as KF08HMN contains letters as first 2, numbers as 3rd and 4th and letters in last 3. The only reference i can find that doesn't involve testing every character with ISNUMBER was on this excel forum

Excel formula to check AlphaNumeric? - Yahoo! Answers

however SUMPRODUCT isn't supported in DAX. There must be an easy way of checking if text fits a certain pattern for validation?

Thanks for any advice.

 

[andiemac]

Not sure if this is DAX supported, but I'd try:

=IF(AND(ISNUMBER(LEFT(A1,2)+0)=FALSE,ISNUMBER(MID(A1,3,2)+0)=TRUE,ISNUMBER(RIGHT(A1,3)+0)=FALSE),"Valid","Not Valid")

Assuming your KF08HMN is in A1.

 

[masplin]

Unfortunately DAX wont allow you to use the ISNUMBER on a piece of text. So this falls over as for first clause it says "cannot convert "KF" of type text to type number. I think you therefore have to wrap each clause in an IFERROR e,g, IFERROR(ISNUMBER(LEFT,A1,2)+0,FALSE). However of course this means the first 2 digits just aren't numbers buts could be "?" or "/". Apart form being painfully long winded with all the IFERROR statements it hasn't actually tested if it is a letter. i have constructed something similar to what you have, but unbelievably complex for what seems like a trivial task and actually it's no correct anyway due to the "letter" test.

 

[masplin]

I adapted your idea replacing the A1 with column name and wrapping each clause in an IFERROR statement and works fine and more elegant than my original version. Looks like this although I have 3 different format to identify so it isn't complete

=IF(
      IFERROR(
                    ISNUMBER(
                                       LEFT([Reg Num Clean],2)+0),
                                       TRUE
                                        )
 && IFERROR( 
                     ISNUMBER(
                                     MID([Reg Num Clean],3,2)+0),
                                     FALSE
                                     )
 && IFERROR(
                       ISNUMBER(
                                   RIGHT([Reg Num Clean],3)+0),
                                   TRUE()
                                            ),
           MID([Reg Num Clean],1,1), "False clause"....

If anyone has any ideas on a neater solution and specially testing if it is actually a letter not just NOT a number I'd be grateful.