Identifying Digit-Only Part Numbers Excluding Special Characters

by Allen Wyatt
(last updated June 9, 2017)

11

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

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

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

MORE FROM ALLEN

Inserting Summary Information

Want to insert into your document those snippets of information that you know Word maintains about your document? It's easy ...

Discover More

Using Find and Replace to Change Text Case

Can you really use Find and Replace to change the case of text in your document? Not really, but that shouldn't stop you from ...

Discover More

Turning Off Screen Updating

When working with macros, you can often speed up processing by turning off the updating of the screen. Best news is that it ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Pulling Initial Letters from a String

When working with names or a different series of words, you may need to pull the initial letters from each word in the ...

Discover More

How Operators are Evaluated

Operators are used in formulas to instruct Excel what to do to arrive at a result. Not all operators are evaluated in the ...

Discover More

Relative References within Named Ranges

Excel is usually more flexible in what you can reference in formulas than is immediately apparent. This tip examines some ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 8Mpixels. 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 7 - 0?

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

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


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.

Newest Tips
Subscribe

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.