Non vba equivalent to ISLIKE?

[merlin777]

is there a non vba equivalent to ISLIKE?

I'm trying to validate a mixed text/numeric entry against 2 critera and this might do the job.

My entry must be in the form 'POnnnnnn' where nnnnnnis between 100000 and 999999 and preceded by 'po' or 'PO'.

 

[Andrew Poulsom]

Here's a starter:

=AND(LEFT(A1,2)="PO",ISNUMBER(RIGHT(A1,6)+0))

 

[squidgeny]

Hi merlin, I answered your other thread about this here:

http://www.mrexcel.com/forum/showthread.php?t=573771

Though it does use a VBA solution.

 

[T. Valko]

is there a non vba equivalent to ISLIKE?

I'm trying to validate a mixed text/numeric entry against 2 critera and this might do the job.

My entry must be in the form 'POnnnnnn' where nnnnnnis between 100000 and 999999 and preceded by 'po' or 'PO'.

Maybe this...

=AND(LEN(A2)=8,LEFT(A2,2)="po",INT(--RIGHT(A2,6))>=100000,INT(--RIGHT(A2,6))<=999999)

 

[T. Valko]

Maybe this...

=AND(LEN(A2)=8,LEFT(A2,2)="po",INT(--RIGHT(A2,6))>=100000,INT(--RIGHT(A2,6))<=999999)

I'm assuming the number must be an integer (whole number).

 

[T. Valko]

I kept trying to quote your reply and it wouldn't work for some reason.

Then I exited the thread to see that you deleted the post!

 

[shg]

Po1.00E5 passes ...

 

[squidgeny]

Po1.00E5 passes ...

lol!

I guess it depends how strict the validation has to be

 

[T. Valko]

Po1.00E5 passes ...

Argh!

I tested a bunch of "E" numbers but not that one!

Lightly tested:

=AND(LEN(A2)=8,LEFT(A2,2)="po",COUNT(-MID(A2,ROW(INDIRECT("2:8")),1))=6,--RIGHT(A2,6)>=100000,--RIGHT(A2,6)<=999999)

 

[T. Valko]

lol!

I guess it depends how strict the validation has to be

There is no "gray area"!