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 num...
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.