Get a number from a string

[Skerdi]

Hi,
Can some1 help me in a query that gets only the numbers from a text field that contains other characters?

If that field has the text 'Bank123' I'd like to have the result only : 123
and if it's 'alphabcd154' the result must be only 154.

thnx,
Skerdi

 

[namliam]

Try using the "Value" function

 

[Rabbie]

Try using the "Value" function

I keep getting an error when I use Value() in A2003. Can you give correct syntax please

 

[namliam]

Must be Oracle on the brain... Val()

But that doesnt seem to pick up the numbers tho

 

[Rabbie]

Thanks for the speedy reply.

Just tested with Val.

Val("123abc") gives 123
Val("abc123") gives 0

Looks like a purpose written function is required.

 

[Dennisk]

use a loop to process each character and if the character isNumeric(expression) then add it to your output string.

Finally convert the output string to numeric.

 

[Mike375]

Try this. That should collect the first group of numbers on the right. Open Query1 on attached db

Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer

For i = 2 To Len(strName)

strTemp = Mid(strName, i, 1)

If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function

 

[Skerdi]

thx guys