Written by Allen Wyatt (last updated February 13, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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, and Excel in Microsoft 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When analyzing data, you may have a need to calculate a sum based on just part of a particular cell. This tip examines ...
Discover MoreTrying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a ...
Discover MoreGot some formulas you slaved over and want to use in lots of workbooks? This tip presents some helpful ideas on how you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-01-13 12:54:26
Roy
Let me help you then Rain.
In my case I need to take part descriptive information and extract the required information. For instance, a simple version would be the string " OD2.345 ID.785 TH.047" and I need the three numbers from that, in order. They could come with ASCII and non-ASCII characters littered in them and could come in metric as well, though never metric and Imperial mixed. They might be out of order and can contain extraneous numbers. Three may be spaces, may not, and there may be extraneous text mixed in. But a simple version could look like the above.
They would normally be presented in various documents, then copied and pasted into the spreadsheet. Never typed in as one would just type what was needed, of course.
So I need all the non-numerical characters stripped out, but not their places as I cannot have the numerals all run together. I need, ideally, to end up with the three values I need, and no others, but I don't bother with that as the times I get a fourth number are fairly rare so we handle those manually. Replacing non-good characters with spaces lets a simple TRIM() strip away the extras.
THAT is one way in which somehow, somewhere in the "world" someone would want or need to do something like that.
The example in the text above is clearly another.
2018-07-02 09:36:35
Rain
Sometimes I wonder, "why in the world would someone need or want to do something like that?" This is one of those times.
2018-06-30 08:46:43
Willy Vanhaelen
I hate those jumbo formulas. So I schoose the UDF but it can be a little simpeler:
Function DigitsOnly_(sRaw As String) As Boolean
Dim X As Integer
For X = 1 To Len(sRaw)
If InStr("0123456789 -/", Mid(sRaw, X, 1)) = 0 Then Exit For
Next X
DigitsOnly = X > Len(sRaw)
End Function
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 © 2023 Sharon Parq Associates, Inc.
Comments