Identifying Digit-Only Part Numbers Excluding Special Characters

Or something as run-of-the-mill as
part # 39-303/147

ok my May 26 comment falls apart because of formula evaluation. However try for example with a part # terminating in //-- and your 2nd formula erroneously says 'Not a legal part #'

Bryan,

I'm not going to argue with you about what I choose to include or not include in my publications. I did, however, want to address the A.V issue you raise...

I appreciate the links you provided to other articles. I agree with you that A.V should generally be avoided if it can be. In fact, your input here allowed me to better understand what you were saying, and it led me to update the A.V article so it is clearer:

http://excelribbon.tips.net/T010598

If you have any other comments on that particular topic (A.V), I invite you to leave them on that article.

Again, thanks.

(And you are correct: The inclusion of A.V on the macro in this particular tip is extraneous because Excel can tell which cells the function is dependent upon.)

-Allen

"Multiple formulas are shown because there are always multiple ways to do things in Excel and people like to see those multiple ways."

Which would be nice if all the ways worked and you explained them all. IFERROR is a weird function, and I'm sure people would like to see how it works. The coercion to a number by multiplying by 1 is a neat little trick, as is treating a number as a boolean; but I only know these things work because I already knew they worked. Adding more just seems like gloating (there was one article where there were literally 20 ways to do something!)

Additionally, in this case, #2 is explicitly worse than #3, because you are adding 2 ISNUMBER operations plus an OR operation. At a minimum you should say "you might be tempted to do this, but this one is better because X".


"[I]t is always good programming practice when you want to make sure that your function runs whenever the worksheet is recalculated"

No no no no! See
http://www.databison.com/index.php/how-to-speed-up-calculation-and-improve-performance-of-excel-and-vba/ (Tip #2)
http://msdn.microsoft.com/en-us/library/office/aa730921%28v=office.12%29.aspx (Ctrl+F search for Application.Volatile)
http://www.decisionmodels.com/calcsecretsi.htm

Reading the article you linked, I can see why you use it all the time: you
misunderstand how it works. A.V doesn't ensure your function runs any time the worksheet is recalculated, it ensures your function runs any time any CELL is recalculated. If you use the "this does not work" version of the function on your A.V page, you will find out that it does, in fact, work! It just doesn't recalculate needlessly.

If you remove Application.Volatile from DigitsOnly() and make cell A1 =DigitsOnly(A2), then change the formula in cell A3, A1 will not recalculate, but if you change cell A2, then A1 will recalculate. This is expected behavior. If you add Application.Volatile back in, then Excel WILL recalculate any time you change cell A3, which is just a waste of processing power. If you have a whole sheet of cells with DigitsOnly()(which is very likely if you have a lot of part numbers) then you will be doing lots of unnecessary recalculation and the spreadsheet will slow to a crawl. If you delete Application.Volatile, Excel will only recalculate cells whose dependents changed.

The only reason you need A.V is when you are relying on data that changes, but isn't an argument variable. For example, if your function refers to Range("A1") (which itself is bad programming practice), Excel won't know to recalculate the function when A1 changes. It also won't know to recalculate things like Date(), Now(), or Rnd(). ONLY when you have this sort of functionality do you need Application.Volatile. (Even then, it is always good to ask yourself if a good ol manual recalc [CTRL+F9] wouldn's suffice).

The same thing goes for internal Excel functions as well. In addition to the above (and their formula analogues), OFFSET(), INDIRECT() and a few others recalculate with every worksheet change and should be used with extreme stinginess.

One more thing! As usual, I've learned something useful to me in this discussion. Being self-taught, I'm often unaware of things like using "Application.Volatile" when writing functions for use in spreadsheets.

Also, I deal with a lot of instances where the "part number" is split into 3 parts as "xxx xxx-xxxx" or "xxx-xxx-xxxx". Now I have a method (Substitute) other than Left(A1,3) & Mid(A1,5,3) & Right(A1,4) to translate before invoking to a VLOOKUP.

Thanks to all!

...and this is when developers argue about the requirements. :)

When I read the requirement "They can also contain special characters such as dashes, slashes, and spaces" I read the "such as" to mean "for example." IOW, the list was not complete and other special characters might be included, e.g. (from my keyboard and still not a complete list: !@#$%^&*()_+="':;?><,.{}[]|.

With that said, here's an alternative IF there might be any other special characters and the requirement is to mark those with a Latin (US/British) alphabetic character.

Function HasAlpha(ByRef PartNo As String) as Boolean
Dim Ch_Ctr as double
Const AlphaChars as String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
HasAlpha = False
For Ch_Ctr = 1 To Len(PartNo)
If InStr(1,AlphaChars,UCase(Mid(PartNo, Ch_Ctr, 1))) > 0 Then
HasAlpha = True
Exit Function
End If
Next Ch_Ctr
End Function


A recognized disadvantage of this function would be that if someone using a different language entered the part number with a letter it might not be one of our 26.

...and now the developers argue about the likelihood of something other than " -/" as speccial characters and someone with a French/German/Spanish/Russian/Greek keyboard is putting in the part numbers. :)

Multiple formulas are shown because there are always multiple ways to do things in Excel and people like to see those multiple ways.

As for using Application.Volatile, it is always good programming practice when you want to make sure that your function runs whenever the worksheet is recalculated. See here:

http://excelribbon.tips.net/T010598

-Allen

I don't see any advantage in showing multiple formulas, when the third one is clearly the best.

And there's that darned Application.Volatile again...

Problem with your 2nd formula
=OR(ISNUMBER(SUBSTITUTE(A1,"-","")+0),ISNUMBER(SUBSTITUTE(A1,"/","")+0),ISNUMBER(SUBSTITUTE(A1," ","")+0))

Assume part # contains one / & one -
First ISNUMBER(SUBSTITUTE removes - and yields false
Second ISNUMBER(SUBSTITUTE removes / and yields false
Third ISNUMBER(SUBSTITUTE remains false therefore the OR returns false :(

Your 3rd formula is quite elegant

Very interesting tip to know how to use SUBSTITUTE function and nested formulas

Love the tip!!!