Pages

Tuesday, March 1

How to extract a number from alphanumeric text string?

Dim mystring, myLength
mystring = "abhikansh567st1239test"
myLength = Len(mystring)

For i = 1 To myLength
    If Asc(Mid(mystring, i, 1)) <> 32 Then
        If Asc(Mid(mystring, i, 1)) >= 48 And Asc(Mid(mystring, i, 1)) <= 57 Then
            myNumber = myNumber & Mid(mystring, i, 1)
        End If
    Else
        msgbox("no numeric")
    End If
Next
msgbox(myNumber)

14 comments:

  1. hello ,
    can u explain what is need of values in loop statements . i.e ,, 32, 48 and 57 . and how it'll extract numbers in a alphanumeric string..

    ReplyDelete
  2. 32, 48 etc are index for ascii character. just try to run the code and see how its working.

    ReplyDelete
  3. with out using functions and loop statement how can we get the number from alpha numerics

    ReplyDelete
  4. What if the length of the string is 1000 ? How to get position of the numbers alone using regular expression?

    ReplyDelete
  5. str = "abhikansh567st1239test"
    Set regEx = New RegExp
    regEx.pattern="\d+"
    regEx.Global = True
    Set obj = regEx.Execute(str)

    For i=0 to obj.count-1

    msgbox obj.item(i)

    Next

    ReplyDelete
  6. Hi by using Regular expressions also we can extract a number from alphanumeric text string.i any one have doubts please let me know

    Set r=new regexp
    r.pattern="[0-9]+"
    r.global=true
    x="koteswararao423"
    Set y=r.execute(x)
    For each z in y
    msgbox z

    ReplyDelete
  7. no need of this much lengthy code...try this

    str="test123abc456"
    for i=1 to len(str)
    c=mid(str,i,1)
    if isnumeric(c) then
    a=a&c
    end if
    next
    msgbox a



    -----Amrendra Jha

    ReplyDelete
    Replies
    1. thanks amrendra.. your code is more simple and optimal compared to others.

      Delete
  8. Little Modification in T .Gopi Code

    str = "abhikansh567st1239test"
    Set regEx = New RegExp
    'regEx.pattern="[0-9]"
    regEx.pattern="\d+"
    regEx.Global = True
    Set obj = regEx.Execute(str)

    For i=0 to obj.count-1
    num=num+obj.item(i)
    Next
    msgbox num

    ReplyDelete
  9. Dim gValue, strvalue
    Dim i
    strvalue= "Th9898 " 'String
    scrstr="898" 'Search String
    strlen=len(strvalue)

    For i= 1 to strlen
    gValue = mid(strvalue,i,1)
    isVaule=IsNumeric(gValue)

    if (isVaule = True) Then
    scrlent=len(scrstr)
    rstr= mid(strvalue,i,scrlent)
    if rstr= scrstr Then
    'Output Value
    Exit For
    End if
    End if
    Next

    ReplyDelete
  10. str="abd123ift"
    msgBox(len(str))
    For i=1 to len(str)
    tempchar=mid(str,i,1)
    If isNumeric(tempchar) Then
    supertempchar=supertempchar&tempchar
    End If
    Next
    msgBox(supertempchar)

    ReplyDelete
  11. can anyone please suggest solution for applying it to entire column in excel sheet.

    ReplyDelete
  12. Guys! and how get first part of numbers in string? For example, if we need only 123 from "abc123 - 4567efg"
    my attempts are futile.

    ReplyDelete
    Replies
    1. str="test123abc456"
      val=0
      for i=1 to len(str)
      c=mid(str,i,1)
      if isnumeric(c) then
      a=a&c
      val=1
      else
      If val=1 Then
      Exit for
      End If

      end if
      next
      msgbox a

      - Diwakar

      Delete