VBA checking to see if a string of text contains a certain syntax

[mwhite0788]

Hello,

What I am trying to accomplish is the following, One of the columns in my spreadsheet has Item Numbers in the form of ABC-123-100X50 for the Prefix ABC and DEF-123-50 for the prefix DEF, What i want to do is have a check that will see if the Prefix is ABC then the string must contain "-","-","X", otherwise it errors or warnings and if it has the Prefix DEF it must contain "-","-" or it errors. Any ideas on how I can accomplish this.

Thanks,

Mike

 

[AOB]

You could try this formula in an adjacent column, or a variation of it according to your requirements

=IF(LEFT(A2,3)="ABC", IF(ISNUMBER(IFERROR(SEARCH("ABC-*-*X*",A2),"ABC Fail")),"ABC Pass","ABC Fail"), IF(LEFT(A2,3)="DEF",IF(ISNUMBER(IFERROR(SEARCH("DEF-*-*",A2),"DEF Fail")),"DEF Pass","DEF Fail"),""))

(Have assumed your original column of codes is in column A and starts at row 2...)

You may want to adjust the find_text arguments of the SEARCH formulas in case I've misinterpreted what it is you're trying to do