Identifying Digit-Only Part Numbers Excluding Special Characters

by Allen Wyatt
(last updated June 9, 2017)


Chris has a large number of cells that contain part numbers. These cells can contain either digits or characters, in any combination. They can also contain special characters such as dashes, slashes, and spaces. Chris needs a way to identify if a cell contains only digits, without taking the special characters into account. Thus, a cell containing 123-45 would show as containing only digits, while 123AB-45 would not.

The easiest way to figure out if a given cell contains only the allowable characters and digits is to use a formula that removes the non-digit permissible characters and then sees if the resulting value is numeric. All of the following formulas can do the trick quite nicely:


You could also use a simple macro to figure out if a cell contains only digits and your allowed characters. While there are any number of ways that such a macro could be approached, here's a rather easy method:

Function DigitsOnly(sRaw As String) As Boolean
    Dim X As Integer
    Const sAllowed As String = "0123456789 -/"

    For X = 1 To Len(sRaw)
       If InStr(sAllowed, Mid(sRaw, X, 1)) = 0 Then Exit For
    Next X
    DigitsOnly = False
    If X > Len(sRaw) Then DigitsOnly = True
End Function

The macro examines whatever is passed to it, comparing each character in the string to a list of allowed characters (in the constant sAllowed). If an disallowed character is detected, the loop is exited early and a False value is returned. Thus, if you wanted to evaluate the cell at A1, you could use the following in your macro:


Since they return either True or False values, any of these approaches (formula or user-defined function) could be used in conjunction with conditional formatting to make formatting changes to your part numbers.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12654) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Printing Documents without Markup

If you have a document with Track Changes turned on, you can accumulate quite a bit of "markup" in it. Here's how you can ...

Discover More

Visually Showing a Protection Status

Need to know if a worksheet or workbook is currently protected? Excel provides some tell-tale signs, but here are some ...

Discover More

Determining the Horizontal Position of the Insertion Point

Need to figure out how far the insertion point is from the left margin? You can do so by using this small macro that ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Summing Every Fourth Cell in a Row

Need to sum a series of cells that fits some regular pattern? Here are several ways that you can get the summation that ...

Discover More

Counting Consecutive Negative Numbers

If you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...

Discover More

Understanding Scope for Named Ranges

When you add a named range to a worksheet, you can specify if you want that named range to apply to the workbook or only ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 0?

2013-06-02 12:39:17


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

2013-06-02 12:17:15


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

2013-05-29 17:59:52



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:

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


2013-05-29 17:06:38


"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 (Tip #2) (Ctrl+F search for Application.Volatile)

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.

2013-05-28 09:35:28


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!

2013-05-28 09:08:34


...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
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. :)

2013-05-28 08:49:47


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:


2013-05-28 07:46:58


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

2013-05-26 10:27:32


Problem with your 2nd formula

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

2013-05-25 21:06:18


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

2013-05-25 09:11:05

Shumeera Jaffer

Love the tip!!!

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.