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.

**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!

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

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

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

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

2013-05-29 17:59:52

awyatt

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

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

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

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

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

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

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.

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

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments