SOLVED

# Checking to see if a serial number follows a certain format

Copper Contributor

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

3 Replies

# Re: Checking to see if a serial number follows a certain format

=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

best response confirmed by galfriday (Copper Contributor)
Solution

# Re: Checking to see if a serial number follows a certain format

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

# Re: Checking to see if a serial number follows a certain format

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

# Re: Checking to see if a serial number follows a certain format

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