IF formula help; if ?=general letter, what = general number

Hi,

Looking for a bit of help with an IF formula. I want to check that a cell contains a specific sequence to ensure the data has been entered correctly, but I'm coming up with errors as I don't know how to just tell excel it needs to look for any number.

e.g. =IF(A1="NR########","PASS","FAIL")

# could be any number, it just has to start with NR and has to have a string of 8 numbers from NR00000001 - NR99999999. The problem I've encountered is that if you just say >NR00000001 and you have NR141 it passes, but it needs to have the full 8 digits after the NR.

Any help would be great!!

Toby

Perhaps =IF(and((left(a1,2)="nr"),len(a1)=10),"PASS","FAIL")

Could it be that the eight digits after NR are not a number ?

EDIT in that case =IF(and((left(a1,2)="nr"),len(a1)=10,isnumber(right(a1,8)),"PASS","FAIL")

=IF(AND(LEN(A1)=10,LEFT(A1,2)="nr",ISNUMBER(RIGHT(A1,8)+0)),"pass","fail")

If you want to ensure that you have NR not nr or Nr then you could amend that to

=IF(AND(LEN(A1)=10,EXACT(LEFT(A1,2),"NR"),ISNUMBER(RIGHT(A1,8)+0)),"pass","fail")

I think this wouldn't work in some conditions.

Say, some one enter NR a space then 7 digit, or NR 7 space one digit

Please Login or Register  to view this content.

Try this one

=IF(AND(LEN(A2)=10,EXACT(LEFT(A2,2),"NR"),COUNT(MID(A2,{3,4,5,6,7,8,9,10},1)+0)=8),"Pass","Fail")