by Allen Wyatt
(last updated June 30, 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:
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, 2013, 2016, 2019, and Excel in Microsoft 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to ...Discover More
If you have two columns containing dates and weights from those dates, you may want to pick a date associated with a ...Discover More
Circular references occur when a formula includes a reference to the cell in which the formula appears. Here's how you ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.