SOLVED

Checking to see if a serial number follows a certain format

Copper Contributor

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.

3 Replies

=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

 

MohammedTayeb_0-1716665388382.png

 

 

best response confirmed by galfriday (Copper Contributor)
Solution

@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.
1 best response

Accepted Solutions
best response confirmed by galfriday (Copper Contributor)
Solution

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

View solution in original post