Ignore letters, recognize numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have model numbers with central numbers, surrounded by letters, like this:
AP9056X
RIC90
HIP80900GXS

What I need is code that will only recognize the numbers...when it fires, it
will return this:
9056
90
80900

Can anyone help me?

Thank you in advance.
Gee
 
I have model numbers with central numbers, surrounded by letters, like this:
AP9056X
RIC90
HIP80900GXS

What I need is code that will only recognize the numbers...when it fires, it
will return this:
9056
90
80900

Can anyone help me?

Thank you in advance.
Gee
Click to expand...

Surrounded by letters, not intermixed?

Create a User Defined function (in a module).

Function ValNumber(strString As String) As Long
On Error GoTo Err_Handler
Dim intX As Integer
Dim intY As Integer
intY = Asc(strString)
Do While intY < 48 Or intY > 57
intX = intX + 1
If intX = Len(strString) Then Exit Do
intY = Asc(Mid(strString, intX, 1))
Loop
If intX = 0 Then intX = 1

ValNumber = Val(Mid(strString, intX))

Exit_ValNumber:
Exit Function
Err_Handler:
MsgBox "Error: " & Err.Number & " " & Err.Description
Resume Exit_ValNumber

End Function

You can call it from a query using:
NewColumn:ValNumber([FieldNaem])

or directrly in an unbound control on a form or report:
=ValNumber([FieldName])
 
Wow...that much huh?
OK...I'll give it a try.
Thank you, very much, for such a quick response!
G

fredg said:
I have model numbers with central numbers, surrounded by letters, like this:
AP9056X
RIC90
HIP80900GXS

What I need is code that will only recognize the numbers...when it fires, it
will return this:
9056
90
80900

Can anyone help me?

Thank you in advance.
Gee
Click to expand...

Surrounded by letters, not intermixed?

Create a User Defined function (in a module).

Function ValNumber(strString As String) As Long
On Error GoTo Err_Handler
Dim intX As Integer
Dim intY As Integer
intY = Asc(strString)
Do While intY < 48 Or intY > 57
intX = intX + 1
If intX = Len(strString) Then Exit Do
intY = Asc(Mid(strString, intX, 1))
Loop
If intX = 0 Then intX = 1

ValNumber = Val(Mid(strString, intX))

Exit_ValNumber:
Exit Function
Err_Handler:
MsgBox "Error: " & Err.Number & " " & Err.Description
Resume Exit_ValNumber

End Function

You can call it from a query using:
NewColumn:ValNumber([FieldNaem])

or directrly in an unbound control on a form or report:
=ValNumber([FieldName])
Click to expand...
 
I'm trying this, but it's not making sense to me.

I'm pretty new to this kind of complicated code so call me an idiot but can
you be more clear?

When "Command9" is clicked, I need it to look at a field called "MOD", pull
out the numbers and put them in a field called "CD".

Thank you for your help...I'm thinking I may need some more training!

G



Gee... said:
Wow...that much huh?
OK...I'll give it a try.
Thank you, very much, for such a quick response!
G

fredg said:
I have model numbers with central numbers, surrounded by letters, like this:
AP9056X
RIC90
HIP80900GXS

What I need is code that will only recognize the numbers...when it fires, it
will return this:
9056
90
80900

Can anyone help me?

Thank you in advance.
Gee
Click to expand...

Surrounded by letters, not intermixed?

Create a User Defined function (in a module).

Function ValNumber(strString As String) As Long
On Error GoTo Err_Handler
Dim intX As Integer
Dim intY As Integer
intY = Asc(strString)
Do While intY < 48 Or intY > 57
intX = intX + 1
If intX = Len(strString) Then Exit Do
intY = Asc(Mid(strString, intX, 1))
Loop
If intX = 0 Then intX = 1

ValNumber = Val(Mid(strString, intX))

Exit_ValNumber:
Exit Function
Err_Handler:
MsgBox "Error: " & Err.Number & " " & Err.Description
Resume Exit_ValNumber

End Function

You can call it from a query using:
NewColumn:ValNumber([FieldNaem])

or directrly in an unbound control on a form or report:
=ValNumber([FieldName])
Click to expand...
Click to expand...
 
Back
Top