software

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.

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), quot;quot;)
Next

For ll = 46 To 47
s = Replace(s, Chr(ll), quot;quot;)
Next

For ll = 58 To 255
s = Replace(s, Chr(ll), quot;quot;)
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 Studentquot;Philquot; wrote:

gt; Hello,
gt;
gt; I have a data field (for a land parcel spreadsheet) that is named Tract
gt; Number. The field is formatted for text, and a sample value would be this:
gt; 7-5-047-088. But there are some cells that contain a combination of numbers
gt; AND text such as 7-5-047-E14, with the letter E in the last group of numbers.
gt;
gt; Is there a way to quickly flag ANY cells that contain Alpha characters?
gt;
gt; I've already tried to use the ISTEXT function, and it doesn't differentiate
gt; between the two types I've already mentioned.
gt;
gt; Thanks in advance for your replies.
gt;
gt; Phil.

Gary''s Student wrote...
gt;I have not thoroughly tested this, but it seems to work:
gt;
gt;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 quot;*[!-0-9]*quot;)
End FunctionFWIW, 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,quot;-quot;,quot;quot;)amp;quot;.0quot;))

The amp;quot;.0quot; 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.Harlan:

Thank you for the advise
--
Gary's Studentquot;Harlan Grovequot; wrote:

gt; Gary''s Student wrote...
gt; gt;I have not thoroughly tested this, but it seems to work:
gt; gt;
gt; gt;Function numbit(r As Range)
gt; ....
gt;
gt; Brute force would be bad enough, but your approach is even less
gt; efficient. As a purely academic exercise, there's a MUCH BETTER way to
gt; check strings in VBA for certain classes of characters, the Like
gt; operator. For example,
gt;
gt;
gt; Function foo(s As String) As Boolean
gt; foo = (Not s Like quot;*[!-0-9]*quot;)
gt; End Function
gt;
gt;
gt; FWIW, the udf above also works in Excel 97 and recent Mac versions
gt; since it doesn't rely on the Replace function, which was added only to
gt; Windows versions with VBA6 in Excel 2000.
gt;
gt; However, no VBA is needed. The following returns TRUE if cell A1
gt; contains nothing but hyphens and decimal numerals.
gt;
gt; =ISNUMBER(-(SUBSTITUTE(A1,quot;-quot;,quot;quot;)amp;quot;.0quot;))
gt;
gt; The amp;quot;.0quot; bit is necessary because one of the OP's examples,
gt; 7-5-047-E14, is a valid number in scientific notation once the hyphens
gt; are removed, but 75047E14.0 isn't valid.
gt;
gt; Never use VBA when the same functionality can be achieved in relatively
gt; short formulas involving no more than a few built-in function calls.
gt; Excessive VBA use is a bad thing.
gt;
gt;

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.

quot;Gary''s Studentquot; wrote:

gt; I have not thoroughly tested this, but it seems to work:
gt;
gt; Function numbit(r As Range)
gt; Dim s As String
gt; s = r.Value
gt; l = Len(s)
gt; For ll = 1 To 44
gt; s = Replace(s, Chr(ll), quot;quot;)
gt; Next
gt;
gt; For ll = 46 To 47
gt; s = Replace(s, Chr(ll), quot;quot;)
gt; Next
gt;
gt; For ll = 58 To 255
gt; s = Replace(s, Chr(ll), quot;quot;)
gt; Next
gt;
gt; If Len(s) = l Then
gt; numbit = True
gt; Else
gt; numbit = False
gt; End If
gt; End Function
gt;
gt; This returns TRUE if the string is only 0 thru 9 and -
gt; otherwise FALSE
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Philquot; wrote:
gt;
gt; gt; Hello,
gt; gt;
gt; gt; I have a data field (for a land parcel spreadsheet) that is named Tract
gt; gt; Number. The field is formatted for text, and a sample value would be this:
gt; gt; 7-5-047-088. But there are some cells that contain a combination of numbers
gt; gt; AND text such as 7-5-047-E14, with the letter E in the last group of numbers.
gt; gt;
gt; gt; Is there a way to quickly flag ANY cells that contain Alpha characters?
gt; gt;
gt; gt; I've already tried to use the ISTEXT function, and it doesn't differentiate
gt; gt; between the two types I've already mentioned.
gt; gt;
gt; gt; Thanks in advance for your replies.
gt; gt;
gt; gt; Phil.

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.

quot;Harlan Grovequot; wrote:

gt; Gary''s Student wrote...
gt; gt;I have not thoroughly tested this, but it seems to work:
gt; gt;
gt; gt;Function numbit(r As Range)
gt; ....
gt;
gt; Brute force would be bad enough, but your approach is even less
gt; efficient. As a purely academic exercise, there's a MUCH BETTER way to
gt; check strings in VBA for certain classes of characters, the Like
gt; operator. For example,
gt;
gt;
gt; Function foo(s As String) As Boolean
gt; foo = (Not s Like quot;*[!-0-9]*quot;)
gt; End Function
gt;
gt;
gt; FWIW, the udf above also works in Excel 97 and recent Mac versions
gt; since it doesn't rely on the Replace function, which was added only to
gt; Windows versions with VBA6 in Excel 2000.
gt;
gt; However, no VBA is needed. The following returns TRUE if cell A1
gt; contains nothing but hyphens and decimal numerals.
gt;
gt; =ISNUMBER(-(SUBSTITUTE(A1,quot;-quot;,quot;quot;)amp;quot;.0quot;))
gt;
gt; The amp;quot;.0quot; bit is necessary because one of the OP's examples,
gt; 7-5-047-E14, is a valid number in scientific notation once the hyphens
gt; are removed, but 75047E14.0 isn't valid.
gt;
gt; Never use VBA when the same functionality can be achieved in relatively
gt; short formulas involving no more than a few built-in function calls.
gt; Excessive VBA use is a bad thing.
gt;
gt;