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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12654) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
The filtering capabilities of Excel are excellent, providing you with great control over which records in a worksheet are ...
Discover MoreIf you have a series of consecutive numbers in a column, you may want to know if it really is consecutive. (In other ...
Discover MoreTrying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-03-28 15:26:35
J. Woolley
Re. my most recent comment below, VBA's Like operator supports limited patterns but a regular expression has more features. Therefore, all these cell formulas match the Tip's DigitsOnly(A1) resultn =NOT(IsRegEx(A1,"[^\d\s/-]+"))n =NOT(IsRegEx(A1,"[^0-9 /-]+"))n =NOT(IsRegEx(A1,"[^0123456789 /-]+"))n =NOT(IsLike(A1,"*[!0123456789 /-]*"))n =NOT(IsLike(A1,"*[!0-9 /-]*"))nSee https://www.tutorialspoint.com/vbscript/vbscript_reg_expressions.htmnand https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/like-operator
2024-03-27 17:49:27
J. Woolley
My Excel Toolbox now includes the following IsRegEx function for convenient use of VBScript.RegExp to compare Text with a regular expression Pattern:nnPublic Function IsRegEx(Text As String, Pattern As String) As Booleann With CreateObject("VBScript.RegExp")n .Pattern = Patternn IsRegEx = .Test(Text)n End WithnEnd FunctionnnReviewing my previous comment below, these cell formulas match the Tip's DigitsOnly(A1) result n =NOT(IsRegEx(A1,"[^0123456789 /-]+"))n =NOT(IsLike(A1,"*[!0123456789 /-]*"))nand these cell formulas help Chris identify cells with part numbers having one or more digits but no letters n =AND(IsRegEx(A1,"\d+"),NOT(IsRegEx(A1,"[a-zA-Z]")))n =AND(IsLike(A1,"*#*"),NOT(IsLike(A1,"*[a-zA-Z]*")))nSee https://sites.google.com/view/MyExcelToolboxnand https://www.tutorialspoint.com/vbscript/vbscript_reg_expressions.htm
2024-03-25 20:48:02
Peter
The regular expression [^\d] matches any data that contains a character that is NOT a digit. This can be implemented with a macro:nnFunction NotDigits(PartNum) As BooleannDim regexn Set regex = CreateObject("VBScript.RegExp")n regex.Pattern = "[^\d]"n NotDigits= regex.Test(PartNum)nEnd Function
2024-03-24 10:23:00
J. Woolley
Here is a another version of the Tip's DigitsOnly function:nnFunction DigitsOnly2(Text As String) As Booleann DigitsOnly2 = Not Text Like "*[!0123456789 /-]*"nEnd FunctionnnAnd here is a more general version that returns TRUE only when a cell's text includes a digit and does not include a letter; Chris might find this more useful:nnFunction DigitsOnly3(Text As String) As Booleann DigitsOnly3 = Text Like "*#*" And Not Text Like "*[a-zA-Z]*"nEnd FunctionnnMy Excel Toolbox includes the following IsLike function for convenient use of VBA's Like operator in a cell formula:nnPublic Function IsLike(Text As String, Pattern As String) As Booleann IsLike = Text Like PatternnEnd FunctionnnTherefore, this cell formula matches the result from DigitsOnly2(A1)n =NOT(IsLike(A1,"*[!0123456789 /-]*"))nand this cell formula matches the result from DigitsOnly3(A1)n =AND(IsLike(A1,"*#*"),NOT(IsLike(A1,"*[a-zA-Z]*")))nSee https://sites.google.com/view/MyExcelToolbox
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 © 2026 Sharon Parq Associates, Inc.
Comments