data 'is not number' character query

[damo_uk]

Hello there,

I am trying to help someone out with a possible formula.

They have a list of data numbers which are yy/#####/@@@@@ (2digit year/casenumber/alphanumeric code)

Where some people have typed in the code wrong (6 or more digits in case number) we want to know is there a way of doing this.

Ideally I want something that says "If 9th character is a slash then yes, if not no" or "if 9th character is a number then yes, if not, no"

If that makes sense?

Example of data is (where we want third option highlighted as erroneous (6 not 5 numbers in middle)

09/56487/KFJT
09/42105/PLOUR3
09/002145/PLIFD
09/02145/ASDF

Many thanks in advance for any ideas..

Damien

[DonkeyOte]

You mean something like ?

B1: =MID(A1,9,1)="/"
copied down (where A1 contains code) ... TRUE means OK, FALSE implies Not OK.

EDIT: should add the above assumes year is always correct... else 0/123456/ABCD would give impression of OK and you would need to account for that. let us know if an issue.

[NBVC]

Try:

=IF(ISNUMBER(MID(A1,9,1)+0),"Yes",IF(MID(A1,9,1)="/","Yes","No"))

[damo_uk]

Ah,

Thought it would be to do with the mid function but was not sure how to write it.

Many thanks for your quick replies! Much appreciated.

[rwgrietveld]

Another approach:

=AND(CODE(MID(A1,3,1))=47,CODE(MID(A1,9,1))=47)