Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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 wrote in message ... 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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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 "RB Smissaert" wrote in message ... 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 wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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: "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbbbb 3bbb" 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 "Jim Cone" wrote in message ... -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) wrote in message 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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
10 thousand, running on a string variable in a VBA Sub, not on a worksheet
range. RBS "Ron Rosenfeld" wrote in message ... On Thu, 30 Aug 2007 16:39:20 -0700, "Jim Cone" wrote: RBS, Thanks for the info. Even when "Like" is slower I still like Like. <g Jim Cone "RB Smissaert" 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: "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbb bb3bbb" 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 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. And Jim's solution (which I really like), returns FALSE for an empty cell. --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
-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) wrote in message 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!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
RBS, Thanks for the info. Even when "Like" is slower I still like Like. <g Jim Cone "RB Smissaert" 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: "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbbbb 3bbb" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
On Thu, 30 Aug 2007 16:39:20 -0700, "Jim Cone" wrote:
RBS, Thanks for the info. Even when "Like" is slower I still like Like. <g Jim Cone "RB Smissaert" 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: "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbbb b3bbb" 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 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. And Jim's solution (which I really like), returns FALSE for an empty cell. --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
On Thu, 30 Aug 2007 20:39:59 -0400, Ron Rosenfeld
wrote: On Thu, 30 Aug 2007 16:39:20 -0700, "Jim Cone" wrote: RBS, Thanks for the info. Even when "Like" is slower I still like Like. <g Jim Cone "RB Smissaert" 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: "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2aaabbbbbbbbb bb3bbb" 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 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. And Jim's solution (which I really like), returns FALSE for an empty cell. --ron 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
And Jim's solution (which I really like), returns FALSE for an empty cell.
--ron 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))))))) ) 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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 wrote in message ... 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!! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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 wrote in message ... 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!! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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))))))) ) 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. What the SUMPRODUCT part is doing is **counting** up the number of digits... Rick |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
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)" wrote in message ... 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 wrote in message ... 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!! |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient way to drtermine if a string contains digits?
I like the first approach. The 2nd one is not very flexible, say if you
wanted to add a period or other misc chars. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This cell contains a date string rep. with only 2 digits for the y | Excel Discussion (Misc queries) | |||
Convert string of digits into a date | Excel Worksheet Functions | |||
Counting Digits in a string | Excel Programming | |||
How do I specific digits in a long string? | Excel Discussion (Misc queries) | |||
Efficient STRING search with SpecialCells | Excel Programming |