IsText and IsNum

[dhruvipanchal88]

Hello,

I'm looking for solution to filter data based on character criteria. I have column name 'Unit'. It contains several string and numeric values. I need  to filter  in script, which has first two characters are letter and then 3 characters are numeric. i.e. 'AB101', 'NV 512' etc.

So far I was trying with below  given but not working out.

 If (len([Unit]) = 5 and IsText(Left(Unit,2) and IsNum(Left(Unit,3)) = 1 and IsNum(Left(Unit,4)) and IsNum(Left(Unit,5))) ,'MUW'

Can anyone suggest here?

Thank You,

Dhruvi

[Or]

If(Len(Unit)=5 and isText(Left(Unit,2)) and IsNum(Mid(Unit,3,3)),ThenSomething,ElseSomething)

Left(Unit,3) would return 'AB1'

[rwunderlich]

@Or 's corrections aside, you made a fundamental error in 

IsNum(Left(Unit,3)) = 1

The result of a true test is "-1", not "1".  Or more commonly written without the =, just as 

IsNum(Left(Unit,3))

-Rob

[dhruvipanchal88]

@rwunderlich  and @Or  Thanks opening up with various idea. 

I'm using the below  statement in my script but it does seems correct. Hope this complete statement would help to correct it.                                                                                                                                                                

If (len([Unit]) = 5 and IsText(Left(Unit,2) and IsNum(mid(Unit,3,3))= -1 and IsNum(mid(Unit,4,4))= -1and IsNum(mid(Unit,5,5)))= -1,'MUW',
If(Left([Unit],1) = '6','EMS',
If(Left([Unit],1) = '7','FIRE',
If(Left([Unit],1) = '8','ASP'))))AS Unitypes

[Or]

Mid(Field,3,3) would return the characters 3 through 5 (three characters starting from the third). You don't need the two additional isnum() checks which are presumably what's causing your issues, and you also don't need to compare to -1 since IsNum() is a boolean (it's either true or false in itself, it doesn't need to be compared to anything).

[dhruvipanchal88]

@Or 

I did those changes. for some reason it is not working out. when I run script there is no error. Still the MUW is not showing for any number. Now the statement looks like                                                                                                                                                                                                 

If (len([Unit]) = 5 and IsText(Left(Unit,2) and IsNum(mid(Unit,3,3))),'MUW',
If(Left([Unit],1) = '6','EMS',
If(Left([Unit],1) = '7','FIRE',
If(Left([Unit],1) = '8','ASP'))))AS Unitypes

[Or]

This is behaving correctly and as expected. It also returns null for ABC12. Your parenthesis look to be in the wrong place.