# Identifying Digit-Only Part Numbers Excluding Special Characters

by Allen Wyatt
(last updated June 8, 2018)

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:

```=IF(IFERROR(INT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-", ""),"/", "")," ", "")),FALSE), TRUE, FALSE)
=OR(ISNUMBER(SUBSTITUTE(A1,"-","")+0),ISNUMBER(SUBSTITUTE(A1,"/","")+0),ISNUMBER(SUBSTITUTE(A1," ","")+0))
=ISNUMBER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"/",""),"-","")*1)
```

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 -/"

Application.Volatile
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 a 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:

```=DigitsOnly(A1)
```

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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

##### MORE FROM ALLEN

Specifying a Font in WordArt

WordArt is a great add-in that allows you to insert creative wording into your document. This tip shows how you can ...

Discover More

Finding Long Sentences

For certain types of writing, you may want to make sure that the sentences in your document do not exceed a certain ...

Discover More

Mysterious Blue Line between Paragraphs

Do you ever have mysterious lines show up between paragraphs either on your screen or on your printouts? It could be ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

##### More ExcelTips (ribbon)

Compiling a List of Students in a Course

Need to pull just a limited amount of information from a large list? Here are a few approaches you might be able to use ...

Discover More

Combining Numbers and Text in a Cell

There are times when it can be beneficial to combine both numbers and text in the same cell. This can be easily done ...

Discover More

Dealing with Circular References

Circular references occur when a formula includes a reference to the cell in which the formula appears. Here's how you ...

Discover More
##### Subscribe

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

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 five more than 8?

2013-06-02 12:39:17

Yvan

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

2013-06-02 12:17:15

Yvan

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

awyatt

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

2013-05-29 17:06:38

Bryan

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

2013-05-28 09:35:28

Don

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

Don

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

2013-05-28 08:49:47

awyatt

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

2013-05-28 07:46:58

Bryan

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

Yvan

=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

2013-05-25 21:06:18

Juan

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.