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)

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..

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

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

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

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

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

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

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

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

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

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)

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

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.

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