Forum Discussion
Solved
Checking to see if a serial number follows a certain format
I am trying to check a cell to see if it has a correct formatting for a serial number. for example, if column b has serial numbers in it, I want to see if they follow a format of 2 letters and 6 numbers.
L=letters
N=numbers
LLNNNNNN
If not, I would like to either have it highlighted or I could put something in another column. Just something to catch my eye for corrections.
I'm not great with formulas and my work excel is, I believe, the 2016 version.
galfriday One possible solution would be to input the following formula at the top of the dataset (in row 2) and copy down:
=AND(LEN(B2)=8, ISERR(--LEFT(B2)), ISERR(--MID(B2,2,1)), ISNUMBER(--RIGHT(B2,6)))You could also use the same formula, nested within the NOT function, to create a Conditional Formatting rule. Please see the attached workbook...
galfriday One possible solution would be to input the following formula at the top of the dataset (in row 2) and copy down:
=AND(LEN(B2)=8, ISERR(--LEFT(B2)), ISERR(--MID(B2,2,1)), ISNUMBER(--RIGHT(B2,6)))You could also use the same formula, nested within the NOT function, to create a Conditional Formatting rule. Please see the attached workbook...
- Thank you so much this worked wonderfully. I'm also going to study the formula to understand it better.
=AND(LEN(B2)=8,ISTEXT(LEFT(B2,2)),NOT(ISNUMBER(VALUE(LEFT(B2,2)))),NOT(ISNUMBER(VALUE(MID(B2,2,1)))),ISNUMBER(VALUE(RIGHT(B2,6))))
Put the formula in the next column, if the serial number in column B has the pattern LLNNNNNN it return TRUE otherwise FALSE
