| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you ID a cell that has both Alpha AND Numeric characters?
Harlan:
Thank you for the advise -- Gary's Student "Harlan Grove" wrote: Gary''s Student wrote... I have not thoroughly tested this, but it seems to work: Function numbit(r As Range) .... Brute force would be bad enough, but your approach is even less efficient. As a purely academic exercise, there's a MUCH BETTER way to check strings in VBA for certain classes of characters, the Like operator. For example, Function foo(s As String) As Boolean foo = (Not s Like "*[!-0-9]*") End Function FWIW, the udf above also works in Excel 97 and recent Mac versions since it doesn't rely on the Replace function, which was added only to Windows versions with VBA6 in Excel 2000. However, no VBA is needed. The following returns TRUE if cell A1 contains nothing but hyphens and decimal numerals. =ISNUMBER(-(SUBSTITUTE(A1,"-","")&".0")) The &".0" bit is necessary because one of the OP's examples, 7-5-047-E14, is a valid number in scientific notation once the hyphens are removed, but 75047E14.0 isn't valid. Never use VBA when the same functionality can be achieved in relatively short formulas involving no more than a few built-in function calls. Excessive VBA use is a bad thing. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Parsing a alpha character out of a cell | Excel Worksheet Functions | |||
| Search string for alpha or numeric | Excel Worksheet Functions | |||
| The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
| Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) | |||
| Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions | |||
Threaded Mode
