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)
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)
hello ,
ReplyDeletecan 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..
32, 48 etc are index for ascii character. just try to run the code and see how its working.
ReplyDeletewith out using functions and loop statement how can we get the number from alpha numerics
ReplyDeleteWhat if the length of the string is 1000 ? How to get position of the numbers alone using regular expression?
ReplyDeletestr = "abhikansh567st1239test"
ReplyDeleteSet 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
Hi by using Regular expressions also we can extract a number from alphanumeric text string.i any one have doubts please let me know
ReplyDeleteSet r=new regexp
r.pattern="[0-9]+"
r.global=true
x="koteswararao423"
Set y=r.execute(x)
For each z in y
msgbox z
no need of this much lengthy code...try this
ReplyDeletestr="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
thanks amrendra.. your code is more simple and optimal compared to others.
DeleteLittle Modification in T .Gopi Code
ReplyDeletestr = "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
Dim gValue, strvalue
ReplyDeleteDim 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
str="abd123ift"
ReplyDeletemsgBox(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)
can anyone please suggest solution for applying it to entire column in excel sheet.
ReplyDeleteGuys! and how get first part of numbers in string? For example, if we need only 123 from "abc123 - 4567efg"
ReplyDeletemy attempts are futile.
str="test123abc456"
Deleteval=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