Results 1 to 16 of 16

Thread: [RESOLVED] Best way to find end of a number in string

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Resolved [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

  2. #2
    PowerPoster
    Join Date
    Aug 2011
    Location
    B.C., Canada
    Posts
    2,887

    Re: Best way to find end of a number in string

    IsNumeric() and use String even if it is a number (in a string).

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    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.

  4. #4
    PowerPoster
    Join Date
    Aug 2011
    Location
    B.C., Canada
    Posts
    2,887

    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()

  5. #5
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Best way to find end of a number in string

    Quote Originally Posted by AlexanderBB View Post
    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" ?

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    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

  7. #7
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    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

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    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

  9. #9
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: Best way to find end of a number in string

    Quote Originally Posted by AlexanderBB View Post
    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.


    Quote Originally Posted by AlexanderBB View Post
    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.


    Quote Originally Posted by AlexanderBB View Post
    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.

  10. #10
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    894

    Re: Best way to find end of a number in string

    Quote Originally Posted by AlexanderBB View Post
    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.

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    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

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    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 " - " ?

  13. #13
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    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

  14. #14

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    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

  15. #15
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    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?

  16. #16

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    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
  •  



Click Here to Expand Forum to Full Width