Written by Allen Wyatt (last updated March 23, 2024)**This tip applies to** Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021

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, 2016, 2019, Excel in Microsoft 365, and 2021.

**Save Time and Supercharge Excel!** Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out *Excel 2010 VBA and Macros* today!

Counting the number of times text occurs within a range of cells can be relatively easy. If you need to only count ...

Discover MoreThe filtering capabilities of Excel are excellent, providing you with great control over which records in a worksheet are ...

Discover MoreTwo rather common trigonometric functions are secants and cosecants. Excel doesn't provide functions to calculate these, ...

Discover More**FREE 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

=NOT(IsRegEx(A1,"[^\d\s/-]+"))

=NOT(IsRegEx(A1,"[^0-9 /-]+"))

=NOT(IsRegEx(A1,"[^0123456789 /-]+"))

=NOT(IsLike(A1,"*[!0123456789 /-]*"))

=NOT(IsLike(A1,"*[!0-9 /-]*"))

See https://www.tutorialspoint.com/vbscript/vbscript_reg_expressions.htm

and https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/like-operator

2024-03-27 17:49:27

J. Woolley

Public Function IsRegEx(Text As String, Pattern As String) As Boolean

With CreateObject("VBScript.RegExp")

.Pattern = Pattern

IsRegEx = .Test(Text)

End With

End Function

Reviewing my previous comment below, these cell formulas match the Tip's DigitsOnly(A1) result

=NOT(IsRegEx(A1,"[^0123456789 /-]+"))

=NOT(IsLike(A1,"*[!0123456789 /-]*"))

and these cell formulas help Chris identify cells with part numbers having one or more digits but no letters

=AND(IsRegEx(A1,"\d+"),NOT(IsRegEx(A1,"[a-zA-Z]")))

=AND(IsLike(A1,"*#*"),NOT(IsLike(A1,"*[a-zA-Z]*")))

See https://sites.google.com/view/MyExcelToolbox

and https://www.tutorialspoint.com/vbscript/vbscript_reg_expressions.htm

2024-03-25 20:48:02

Peter

Function NotDigits(PartNum) As Boolean

Dim regex

Set regex = CreateObject("VBScript.RegExp")

regex.Pattern = "[^\d]"

NotDigits= regex.Test(PartNum)

End Function

2024-03-24 10:23:00

J. Woolley

Function DigitsOnly2(Text As String) As Boolean

DigitsOnly2 = Not Text Like "*[!0123456789 /-]*"

End Function

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

Function DigitsOnly3(Text As String) As Boolean

DigitsOnly3 = Text Like "*#*" And Not Text Like "*[a-zA-Z]*"

End Function

My Excel Toolbox includes the following IsLike function for convenient use of VBA's Like operator in a cell formula:

Public Function IsLike(Text As String, Pattern As String) As Boolean

IsLike = Text Like Pattern

End Function

Therefore, this cell formula matches the result from DigitsOnly2(A1)

=NOT(IsLike(A1,"*[!0123456789 /-]*"))

and this cell formula matches the result from DigitsOnly3(A1)

=AND(IsLike(A1,"*#*"),NOT(IsLike(A1,"*[a-zA-Z]*")))

See 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 © 2024 Sharon Parq Associates, Inc.

## Comments