-
Jun 2nd, 2013, 09:10 PM
#1
Thread Starter
Frenzied Member
[RESOLVED] Best way to find end of a number in string
Hi,
I'm wondering if Filter or regular expression or substring is possible, or if looping through char by char is the way.
What is sought is the position in the string of the first non-numeric character. Then I can extract the number only.
VAL is no good as it loses leading zeros and will skip a space.
I need to know e.g. if the number is "0001" or "001" And "0001 11" not be 111.
LOL sorry if crazy question but hard to explain!
Thanks, ABB
-
Jun 2nd, 2013, 09:45 PM
#2
Re: Best way to find end of a number in string
IsNumeric() and use String even if it is a number (in a string).
-
Jun 2nd, 2013, 09:57 PM
#3
Thread Starter
Frenzied Member
Re: Best way to find end of a number in string
That gives True or False. I need 'getNumeric'... and it's length with any leading zeros.
I can remove any spaces and use VAL but still can't tell how many leading zero's. Guess there's many ways
to do it but wondering which is best/fastest. Or If using a loop is a good as any.
-
Jun 2nd, 2013, 10:28 PM
#4
Re: Best way to find end of a number in string
Loop would be a good way, here is a quick example, might not be the best but works good.
Code:
Private Sub Command1_Click()
Dim bNumeric As Boolean
Dim MyString As String
Dim NumberInString() As String
Dim iCount As Integer: iCount = -1
Dim i As Integer
MyString = "Number is 0001 or 000001111100 and can still be extracted from a string!"
Do Until i = Len(MyString)
i = i + 1
If IsNumeric(Mid(MyString, i, 1)) = True Then
If bNumeric = False Then
bNumeric = True
iCount = iCount + 1
ReDim Preserve NumberInString(iCount)
End If
NumberInString(iCount) = NumberInString(iCount) & Mid(MyString, i, 1)
Else
bNumeric = False
End If
Loop
For i = 0 To UBound(NumberInString)
Debug.Print NumberInString(i)
Next
End Sub
Note:
You could then convert it into whatever you'd like CInt(), CDbl(), CSng(), CDec(), CLng()
-
Jun 3rd, 2013, 01:51 AM
#5
Re: Best way to find end of a number in string
Originally Posted by AlexanderBB
Hi,
What is sought is the position in the string of the first non-numeric character. Then I can extract the number only.
VAL is no good as it loses leading zeros and will skip a space.
I need to know e.g. if the number is "0001" or "001" And "0001 11" not be 111.
What results would you expect from numbers such as:
Code:
"0001" - "0001" or Null (since there's no non-numeric characters in the original))?
"0001 11" - "11" or " 11" ?
"0001 11 12" - "11" or "12" or "11 12" ?
-
Jun 3rd, 2013, 03:55 AM
#6
Thread Starter
Frenzied Member
Re: Best way to find end of a number in string
Thanks Max. That works.. I still think there should be a way to do it without looping, but I could be wrong!
> What results would you expect from numbers such as:
Hi Doogle
>"0001" - "0001" or Null (since there's no non-numeric characters in the original))?
0001
>"0001 11" - "11" or " 11" ?
0001
>"0001 11 12" - "11" or "12" or "11 12" ?
0001 again.
Maybe I worded the question wrong, it would be VAL but including leading zeros. Then perhaps splitting on " " first.
I was doing something similar to Max but using Select Case instead of IsNumeric.
Here
-
Jun 3rd, 2013, 06:24 AM
#7
Re: Best way to find end of a number in string
Regular expression is a good solution in this case.
Code:
Private Sub Command1_Click()
Dim objRegExp
Dim objMatch
Dim colMatches
Dim strTestInput As String
strTestInput = "Number is 0001 or 000001111100 and can still be extracted from a string!"
' Create a regular expression object.
Set objRegExp = CreateObject("VBScript.RegExp")
'Set the pattern.
objRegExp.Pattern = "\d+"
'Set global applicability.
objRegExp.Global = True
'Test whether the String can be compared.
If (objRegExp.Test(strTestInput) = True) Then
'Get the matches.
Set colMatches = objRegExp.Execute(strTestInput)
' Iterate Matches collection.
For Each objMatch In colMatches
Debug.Print objMatch.Value
Next
Else
Debug.Print "No numbers"
End If
End Sub
-
Jun 3rd, 2013, 06:47 AM
#8
Thread Starter
Frenzied Member
Re: Best way to find end of a number in string
Hi Mark,
That got it wonderfully - many thanks
Couple of questions - do I need global applicability and under what
circumstances would Each objMatch In colMatches be more than 1 ?
I can't fathom why objRegExp.pattern is "\d+" either... that's quite as mystery!
Regards, ABB
-
Jun 3rd, 2013, 07:04 AM
#9
Re: Best way to find end of a number in string
Originally Posted by AlexanderBB
do I need global applicability
When set to true all matches will be returned in the match collection. In this case it would return a match on 0001 and a match on 000001111100. If set to false (the default) it would only return the first match. 0001 in this case.
Originally Posted by AlexanderBB
under what circumstances would Each objMatch In colMatches be more than 1
If there is more than one match and global is set to true.
Originally Posted by AlexanderBB
I can't fathom why objRegExp.pattern is "\d+" either... that's quite as mystery!
Regular expression syntax is screwy. In this case \d will return a numeric. The + returns one or more instances in the group. \d is shorthand for [0-9]. If instead you used \D all non-numerics would be returned.
-
Jun 3rd, 2013, 07:17 AM
#10
Fanatic Member
Re: Best way to find end of a number in string
Originally Posted by AlexanderBB
Hi Mark,
That got it wonderfully - many thanks
Couple of questions - do I need global applicability and under what
circumstances would Each objMatch In colMatches be more than 1 ?
I can't fathom why objRegExp.pattern is "\d+" either... that's quite as mystery!
Regards, ABB
you must deal with binaries written in textual form?
If it is true, the faster way is to detect the first 1 or 0, and then looks for the end of the numeric expression.
Code:
dim p1 as long, p2 as long
p1 = instr(string1, "0"):if p1<1 then p1=2^30
p2= instr(string1,"1"):if p2<1 then p2=2^30
if p1<p2 then
' there is something and the expression starts is pointed by p1.
else
if p2<2^30 then
' there is something and the expression starts is pointed by p2.
endif
endif
' so this way you don't loops in a discrete way, being it faster to find the start of the numeric expression.
then you needs to find the first character that isn't zero or one, and stops there, it is a smaller tiny loop.
-
Jun 3rd, 2013, 07:58 AM
#11
Thread Starter
Frenzied Member
Re: [RESOLVED] Best way to find end of a number in string
Hi Mark,
It gets better and better - I never need more than the first match so I can remove the loop.
It also works in Excel VBA and the \D switch is needed too. In fact I would have used the \d to replace result with "" to get the non-numerics.
This will save lines and lines of code !! I knew regular expressions were amazing and this proves it.
Hi flyguille, will play with that but they're not binaries. That was just an example to show leading zeros.
Beers all round - great result :-)
Thanks again, ABB
-
Jun 3rd, 2013, 08:13 AM
#12
Thread Starter
Frenzied Member
Re: [RESOLVED] Best way to find end of a number in string
Mark, could your objRegExp.Pattern detect everything after "_" and before " - " in a string ?
Or, just everything before " - " ?
-
Jun 3rd, 2013, 08:48 AM
#13
Re: [RESOLVED] Best way to find end of a number in string
Lets see if I read your question correctly.
Code:
Private Sub Command1_Click()
Dim objRegExp
Dim objMatch
Dim colMatches
Dim strTestInput As String
strTestInput = "Number is 0001 or 000001111100 and - can still be _ extracted from a string!"
' Create a regular expression object.
Set objRegExp = CreateObject("VBScript.RegExp")
'Set the pattern.
objRegExp.Pattern = "-(.*?)_"
'Set global applicability.
objRegExp.Global = True
'Test whether the String can be compared.
If (objRegExp.Test(strTestInput) = True) Then
' Use Replace to remove
Debug.Print objRegExp.Replace(strTestInput, "")
End If
End Sub
-
Jun 3rd, 2013, 09:36 AM
#14
Thread Starter
Frenzied Member
Re: [RESOLVED] Best way to find end of a number in string
Mark, I got no result printed, but maybe I should give you the actual criteria and conditions.
I was looking for a sub-result and do the rest myself, but that may be the right approach with Reg Expression power !
strTestInput = "u64_002b - gggg"
However the 'u' may or may not be there.
The '64' may be 1964 (or 2013)
the '002' may be '0002'
The 'b' may have a (single) number after it. ('b' can be anything from a-z and also za-zz, following from z)
Whatever is after " - " is irrelevant. The "_" will always be present.
Each of these would ideally be checked for, and yes I hope to develop a Class Module where I could send in
strTestInput as a substring of a longer string and still get the various parameters back. May not be needed now though
-
Jun 3rd, 2013, 09:42 AM
#15
Re: [RESOLVED] Best way to find end of a number in string
I'm still not sure what you are trying to get. How about a few different input examples along with the expected output?
-
Jun 3rd, 2013, 10:01 AM
#16
Thread Starter
Frenzied Member
Re: [RESOLVED] Best way to find end of a number in string
Mark, I think I can do it from what you've already given me.
u1956_002b3 = 002 + 1956 + b + 3
2002_0004 = 0004 + 2002
53_055zc = 055 = [19]53 + zc
I didn't really mean to go this far with the question, but got exited with the results!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|