Logic test for cell value in a certain format?

[Arnold Layne]

Please see my attached sample file...

I am trying to come up with a formula that will return "TRUE" if the preceding cell contains a properly formatted part number, and "FALSE" if the cell contains any letters, text, or numbers formatted differently than a proper part number.

What I mean by a "proper" part number is a number formatted as four digits, then a dash, and then four more digits, i.e. "1010-0023".

So far, I've managed to get FALSE if any characters other than "1234567890-" are present in cell A2, and true if cell A2 only contains numbers and/or a "-", using this formula in cell B2:

Please Login or Register  to view this content.

However, any number in A1 that is not correctly formatted, but only contains numbers and or dashes, also returns TRUE. So, values like the following need to generate a "FALSE", but don't:


549824

11384736

11325-34562

6-5-4-3-2

103-0224


And so on. I'm sure there's a way to do this, hopefully without resorting to scripts and VBA, but I'm open to any option. Please help!

Thanks!

[TMS]

Try:

Please Login or Register  to view this content.

So, length is 9, hyphen is in position 5, and the cell value is numeric when the hyphen is removed.

[Ghozi Alkatiri]

at B2
=IF(LEN(A2)<>9,FALSE,ISNUMBER(VALUE(LEFT(A2,FIND("-",A2)-1)&MID(A2,FIND("-",A2)+1,LEN(A2)))))
copy down

[Arnold Layne]

Both suggestions work perfectly! I went with TMS's method, only because I was able to change the {1,0} at the end to {"YES","NO"} to better suit my full project. But both options are genius!

Thanks, guys!

[TMS]

You're welcome. Thanks for the rep.


To be fair, I thought you had 0s and 1s. I'd probably go with "yes" and blank, but you can do with it what your heart desires