Efficient way to drtermine if a string contains digits?

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
R

RB Smissaert

This might suit you and it is fast:

Function PositionFirstNumberInString(strString As String) As Long

Dim i As Long
Dim btArray() As Byte

btArray = strString

For i = 0 To UBound(btArray) Step 2
If btArray(i) > 47 And btArray(i) < 58 Then
PositionFirstNumberInString = i \ 2 + 1
Exit Function
End If
Next

PositionFirstNumberInString = -1

End Function


Use it like this:

If PositionFirstNumberInString("aaaaa2bbbbb3mmmmm") > -1 Then
Msgbox "this string has a digit, so answer is True"
End If

You could change the function to a Boolean output if you want, but it makes
sense
to keep the position information.


RBS
 
Actually, this is faster:

Function PositionFirstNumberInString2(strString As String) As Long

Dim i As Long
Dim lPos As Long

For i = 0 To 9
lPos = InStr(1, strString, i, vbBinaryCompare)
If lPos > 0 Then
PositionFirstNumberInString2 = lPos
Exit Function
End If
Next i

PositionFirstNumberInString2 = -1

End Function


RBS
 
Yes, that is the fastest, but of course it doesn't give the position of the
first digit.

Did some timing, running on this string:
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbbbb3bbb"

Jim 4 msecs
RBS 24 msecs
Ron 5200 msecs

Of course the VBScript method will be a lot faster if the first 3 lines of
code are taken out of the function.


RBS
 
10 thousand, running on a string variable in a VBA Sub, not on a worksheet
range.

RBS
 
What is the most efficient way to determine if any of the characters of a
string are digits?

One example would be a function that returns "TRUE" if one or more
characters of its single argument are digits and "FALSE" otherwise.

Thanks!!
 
What is the most efficient way to determine if any of the characters of a
string are digits?

One example would be a function that returns "TRUE" if one or more
characters of its single argument are digits and "FALSE" otherwise.

Thanks!!
Click to expand...

Worksheet formula:

=NOT(ISNA(LOOKUP(9.9+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))))

VBA Function

=============================
Option Explicit
Function Num(str As String) As Boolean
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d"
Num = re.test(str)
End Function
=========================
--ron
 
-Or-

Function WhoHowWhat(ByRef str As String) As Boolean
WhoHowWhat = (str Like "*#*")
End Function
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




What is the most efficient way to determine if any of the characters of a
string are digits?
One example would be a function that returns "TRUE" if one or more
characters of its single argument are digits and "FALSE" otherwise.
Thanks!!
 
RBS,
Thanks for the info.
Even when "Like" is slower I still like Like. <g>
Jim Cone


"RB Smissaert"
<[email protected]>
wrote in message
Yes, that is the fastest, but of course it doesn't give the position of the
first digit.
Did some timing, running on this string:
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbbbb3bbb"

Jim 4 msecs
RBS 24 msecs
Ron 5200 msecs

Of course the VBScript method will be a lot faster if the first 3 lines of
code are taken out of the function.
RBS
 
RBS,
Thanks for the info.
Even when "Like" is slower I still like Like. <g>
Jim Cone


"RB Smissaert"
<[email protected]>
wrote in message
Yes, that is the fastest, but of course it doesn't give the position of the
first digit.
Did some timing, running on this string:
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbbbb3bbb"

Jim 4 msecs
RBS 24 msecs
Ron 5200 msecs

Of course the VBScript method will be a lot faster if the first 3 lines of
code are taken out of the function.
RBS
Click to expand...

How many iterations did you time?

And I would guess that my formula solution would be even faster, based on the
time to complete calculations over 65534 cells.

Also, as a small nit, RBS solution does not do what the OP suggested in his
example.
One example would be a function that returns "TRUE" if one or more
characters of its single argument are digits and "FALSE" otherwise.
Click to expand...

And Jim's solution (which I really like), returns FALSE for an empty cell.
--ron
 
How many iterations did you time?

And I would guess that my formula solution would be even faster, based on the
time to complete calculations over 65534 cells.

Also, as a small nit, RBS solution does not do what the OP suggested in his
example.


And Jim's solution (which I really like), returns FALSE for an empty cell.
--ron
Click to expand...

Hit send too soon. My formula solution would need to be modified to do that:

=IF(A1<>"",NOT(ISNA(LOOKUP(9.9+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))))
--ron
 
And Jim's solution (which I really like), returns FALSE for an empty cell.
Hit send too soon. My formula solution would need to be modified to do
that:

=IF(A1<>"",NOT(ISNA(LOOKUP(9.9+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))))
Click to expand...

Here is a slightly shorter formula I came up with to do this...

=SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),"1234567890")))>0

What the SUMPRODUCT part is doing is summing up all the digits in A1, so
seeing if that is greater than zero gives the result the OP asked for.

Rick
 
Another way, a reusable function that enables you to modify to the chars
allowed in the text, now limited to numbers:

Sub Test2()
MsgBox HasCharAllowed("3 test")
End Sub

Function HasCharAllowed(ByVal s As String) As Boolean
Const CharsAllowed = "0123456789"
Dim i As Integer
HasCharAllowed = False
For i = 1 To Len(s)
If InStr(CharsAllowed, Mid(s, i, 1)) > 0 Then
HasCharAllowed = True
Exit For
End If
Next
End Function
 
You can use this function instead....

Function HasCharAllowed(ByVal S As String) As Boolean
Const CharsAllowed = "0123456789"
HasCharAllowed = S Like "*[" & CharsAllowed & "]*"
End Function

And, if you want, you can even shorten the Const statement to this...

Const CharsAllowed = "0-9"

Other characters can be added to the allowed character list as long as the
"special" ones described in the help files for the Like operator are handled
as mentioned there.

Rick
 
Hit send too soon. My formula solution would need to be modified to do
Here is a slightly shorter formula I came up with to do this...

=SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),"1234567890")))>0

What the SUMPRODUCT part is doing is summing up all the digits in A1, so
seeing if that is greater than zero gives the result the OP asked for.
Click to expand...

What the SUMPRODUCT part is doing is **counting** up the number of digits...

Rick
 
I like the first approach. The 2nd one is not very flexible, say if you
wanted to add a period or other misc chars.


Rick Rothstein (MVP - VB) said:
You can use this function instead....

Function HasCharAllowed(ByVal S As String) As Boolean
Const CharsAllowed = "0123456789"
HasCharAllowed = S Like "*[" & CharsAllowed & "]*"
End Function

And, if you want, you can even shorten the Const statement to this...

Const CharsAllowed = "0-9"

Other characters can be added to the allowed character list as long as the
"special" ones described in the help files for the Like operator are
handled as mentioned there.

Rick

Another way, a reusable function that enables you to modify to the chars
allowed in the text, now limited to numbers:

Sub Test2()
MsgBox HasCharAllowed("3 test")
End Sub

Function HasCharAllowed(ByVal s As String) As Boolean
Const CharsAllowed = "0123456789"
Dim i As Integer
HasCharAllowed = False
For i = 1 To Len(s)
If InStr(CharsAllowed, Mid(s, i, 1)) > 0 Then
HasCharAllowed = True
Exit For
End If
Next
End Function
Click to expand...
Click to expand...
 
I like the first approach. The 2nd one is not very flexible, say if you
wanted to add a period or other misc chars.
Click to expand...

Just add them to the list. For example... period, comma, equal sign, dash:

Const CharsAllowed = "0-9.,=-"

Another example... is there a Hex digit in the string (upper or lower case
letters permitted):

Const CharsAllowed = "0-9A-Fa-f"

The use of ranges can cut down on typing lots of characters.

Rick
 
Back
Top