Determine Text Contents

[MASONS4]

Hi,

I've been struggling with a formula all afternoon, and am in desperate need of help for my own sanity.

I have a workflow system which automatically generates references. The system will generate a reference which is always prefixed "GOC-" and then will be followed by 6 numbers.

The issue is that users can also generate non-standard references (i.e. user generated references are free form).

So I'm trying to determine which are system ("First") generated and which are user ("Rework").

The formula I've been working on should check:

The first digits in each cell is "GOC-" a
The total length of the cell is 10 characters
Tthe last 6 digits in the cell are numeric, but it's not working.

=(IF(AND(FIND("GOC-",H2),(LEN(H2)=10),(ISNUMBER(RIGHT(H2,5)))),"First","Rework"))

The entry in cell H2 is GOC-229702, so it should return a value of "First"

Any help would be greatly appreciated.

Thanks in advance.

Sean

 

[njimack]

RIGHT returns a text string, even if it's made up of numeric characters. Try this:
=(IF(AND(FIND("GOC-",H2),(LEN(H2)=10),(NOT(ISERROR(RIGHT(H2,5)+0)))),"First","Rework"))

 

[jtakw]

Hi,

Just an observation, I thought we were checking the right 6 digits, not 5.

 

[MASONS4]

Just tried it and it worked perfectly. Thank you very much for your help.

 

[MASONS4]

Ha - thanks for noticing my "deliberate" error! Have fixed that as well