Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you ID a cell that has both Alpha AND Numeric characters?
Hello,
I have a data field (for a land parcel spreadsheet) that is named Tract Number. The field is formatted for text, and a sample value would be this: 7-5-047-088. But there are some cells that contain a combination of numbers AND text such as 7-5-047-E14, with the letter E in the last group of numbers. Is there a way to quickly flag ANY cells that contain Alpha characters? I've already tried to use the ISTEXT function, and it doesn't differentiate between the two types I've already mentioned. Thanks in advance for your replies. Phil. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you ID a cell that has both Alpha AND Numeric characters?
I have not thoroughly tested this, but it seems to work:
Function numbit(r As Range) Dim s As String s = r.Value l = Len(s) For ll = 1 To 44 s = Replace(s, Chr(ll), "") Next For ll = 46 To 47 s = Replace(s, Chr(ll), "") Next For ll = 58 To 255 s = Replace(s, Chr(ll), "") Next If Len(s) = l Then numbit = True Else numbit = False End If End Function This returns TRUE if the string is only 0 thru 9 and - otherwise FALSE -- Gary's Student "Phil" wrote: Hello, I have a data field (for a land parcel spreadsheet) that is named Tract Number. The field is formatted for text, and a sample value would be this: 7-5-047-088. But there are some cells that contain a combination of numbers AND text such as 7-5-047-E14, with the letter E in the last group of numbers. Is there a way to quickly flag ANY cells that contain Alpha characters? I've already tried to use the ISTEXT function, and it doesn't differentiate between the two types I've already mentioned. Thanks in advance for your replies. Phil. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you ID a cell that has both Alpha AND Numeric characters?
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. |
#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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you ID a cell that has both Alpha AND Numeric characters?
Gary,
First off, Thanks for your reply. Second, as it turns out, I am not familiar enought with VBA to know where to take your code and incorporating your code into my situation, other than I DO know that I'd need to copy it into a new module (or something like that). That being said, I WOULD like to know what steps there are to make this happen, so I can learn how to (at least) take someone else's code and apply it to my situation. So, for now, all I have done is opened the VBA editor, created a new module (under personal.xls), pasted the code from your post reply, and called the module numbit. Then what? Waiting for your reply... Phil. "Gary''s Student" wrote: I have not thoroughly tested this, but it seems to work: Function numbit(r As Range) Dim s As String s = r.Value l = Len(s) For ll = 1 To 44 s = Replace(s, Chr(ll), "") Next For ll = 46 To 47 s = Replace(s, Chr(ll), "") Next For ll = 58 To 255 s = Replace(s, Chr(ll), "") Next If Len(s) = l Then numbit = True Else numbit = False End If End Function This returns TRUE if the string is only 0 thru 9 and - otherwise FALSE -- Gary's Student "Phil" wrote: Hello, I have a data field (for a land parcel spreadsheet) that is named Tract Number. The field is formatted for text, and a sample value would be this: 7-5-047-088. But there are some cells that contain a combination of numbers AND text such as 7-5-047-E14, with the letter E in the last group of numbers. Is there a way to quickly flag ANY cells that contain Alpha characters? I've already tried to use the ISTEXT function, and it doesn't differentiate between the two types I've already mentioned. Thanks in advance for your replies. Phil. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you ID a cell that has both Alpha AND Numeric characters?
Harlan,
Like I replied to Gary's post, I do not know enough about VBA to be able to take the code and make it work for my situation, but if you want to tell me how to take it and make it into a working macro, I'm all ears. Nonetheless, I WAS able to take the ISNUMBER function you provided, and it worked GREAT! Thank you. Phil. "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. |
Reply |
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 |