Solved: AlphaNumber Formula

[sujittalukde]

I want a formula to check the TRUE or FALSE for the following structure:

The text structure is as given : AAVCF5678G

The Length is 10
Left 5 are characters from A to Z
Next four are Numbers from 0 to 9
Last one is Character (A to Z)

How do I design a formula so that the result will come TRUE if input is correct and FALSE if input does not match the said criteria?

Regards
Sujit Talukder

[Bob Phillips]

Try

=AND(ISTEXT(LEFT(A2,5)),ISNUMBER(--MID(A2,6,4)),ISTEXT(RIGHT(A2,1)),NOT(ISNUMBER(FIND({".",",",":",";","\","/","!"},A2))))

you can add other not-allowed characters.

[sujittalukde]

Thanks xld. But this give result = TRUE even if in the Text entered in cell A2 as "AAVCF5 ". I wnat result to TRUE if full length of the text is entered. that is to say, when full AAVCF5678G is correctly entered then result should come TRUE else FALSE.

[Bob Phillips]

Just add a length test

=AND(LEN(A2)=10,ISTEXT(LEFT(A2,5)),ISNUMBER(--MID(A2,6,4)),ISTEXT(RIGHT(A2,1)),NOT(ISNUMBER(FIND({".",",",":",";","\","/","!"},A2))))

[Aussiebear]

Here we go again, another of NASA's launch codes. I'm trying to break this down and getting confused..... I'm assuming that;

To test for correct length of the string is the LEN(A2)=10, and
To test for first 5 characters to be text is ISTEXT(LEFT(A2,5)), and
To test for numerals starting at position 6 is ISNUMBER(--MID(A2,5,4)), and
To test for last character in string is ISTEXT(RIGHT(A2,1), and
To test for non acceptable characters is NOT(ISNUMBER(Find([".",",",:",";","\","/"!"}, and
If any of these test are false, return A2.

Can I therefore also assume a single AND can encompass more than two tests? Is there a limit to the number of tests one can imply?

Next question relates to the non acceptable characters. Bob used NOT(ISNUMBER, yet the characters are not Alphabetical either. Are they therefore regarded as TEXT?

[mikerickson]

To accept to A-Z and reject 1,2,3,#,$,a,b,etc (and to check for length >9)

=AND(65<=MIN(CODE(MID(A1&"xxxxxxxxxx",{1,2,3,4,5,10},1))), MAX(CODE(MID(A1&"xxxxxxxxxx",{1,2,3,4,5,10},1)))<=90)

Edit:

Expanding on that, I think this will do what you ask
=AND({65,65,65,65,65, 48,48,48,48, 65}<=CODE(MID(A1,{1,2,3,4,5,6,7,8,9,10},1)), _
CODE(MID(A1,{1,2,3,4,5,6,7,8,9,10},1))<={90,90,90,90,90, 57,57,57,57, 90}, _
LEN(A1)=10)

[Aussiebear]

I've ordered a doctor for you Mike.... . You are going to have to break this one down into something simple for me.

[Bob Phillips]

To test for correct length of the string is the LEN(A2)=10, and
To test for first 5 characters to be text is ISTEXT(LEFT(A2,5)), and
To test for numerals starting at position 6 is ISNUMBER(--MID(A2,5,4)), and
To test for last character in string is ISTEXT(RIGHT(A2,1), and
To test for non acceptable characters is NOT(ISNUMBER(Find([".",",",:",";","\","/"!"}, and
If any of these test are false, return A2.

Almost., it returns TRUE or FALSE, not A2

Can I therefore also assume a single AND can encompass more than two tests? Is there a limit to the number of tests one can imply?

Absolutely it can. I have no idea what the limits might be, never been anywhere near that.

Next question relates to the non acceptable characters. Bob used NOT(ISNUMBER, yet the characters are not Alphabetical either. Are they therefore regarded as TEXT?

They are not numeric, but FIND returns the numeric position of the character, or returns #VALUE!. so we are testing that for numeric not the values themselves.

[mikerickson]

I've ordered a doctor for you Mike.... . You are going to have to break this one down into something simple for me.

MID(A1,{1,2,3,4,5,6,7,8,9,10},1)
is an array of each of character in A1
CODE(MID(A1,{1,2,3,4,5,6,7,8,9,10},1)) is the ascii values for those characters

{65,65,65,65,65, 48,48,48,48, 65}<=CODE(MID(A1,{1,2,3,4,5,6,7,8,9,10},1))

Is an array of True/False values if the characters are above their limits.

It would be easier to see in this formulation:

{"A","A","A","A","A","0","0","0","0","A"}<=MID(A1,{1,2,3,4,5,6,7,8,9,10} ,1)
returns an array of true/false values

AND({"A","A","A","A","A","0","0","0","0","A"}<=MID(A1,{1,2,3,4,5,6,7,8,9,10 },1)) evaluates to TRUE only when

"A" <= first character , and
"A" <= second character , and
...
"0" <= fifth character , and
"0" <= sixth character , and
...
"A" <= last character

Combining a second condtion
=AND(_
({"A","A","A","A","A","0","0","0","0","A"}<=MID(A1,{1,2,3,4,5,6,7,8,9,10},1 )), _
(MID(A1,{1,2,3,4,5,6,7,8,9,10},1)<={"Z","Z","Z","Z","Z","9","9","9","9","Z" }))

is true only if
"A" <= first character AND first character <= "Z" , and
"A" <= second character AND second character <= "Z" , and
...
"0" <= fifth character AND fifth character <= "Z" , and
"0" <= sixth character AND sixth character <= "Z" , and
...
"A" <= last character AND last character <= "Z"

That is if each character is of the correct type, the formula returns TRUE, False otherwise.

NOTE. The approach in this post (without CODE) is case insensitive since "r"<"Z"
Using the CODE formulation above is case sensitive.

(Adding the "xxxxxxxxxx" prevents a #VALUE error when A1 is shorter than 10. Any string of 10 illegal characters would work.)

[sujittalukde]

thanks to all the reply and solution.

[Aussiebear]

@Bob, thank you for your advice, and

@Mike, Your explanation will take a few re reads to comprehend. Maybe when I'm more alert. But thanks anyway.