separate numbers and text

[Anthony]

Hi,

Any idea's for this problem:

I would like to separate numbers and text from a field.
e.g. In a record displaying ds/10st. , i would like 'remove' all text
characters.
so the result will be just "10"
How is this done ???
I've tried several things for days now with the "BuilD" option in query's
but with no luck. Can this be done with Trim$ or Remove,,,,,
please help me out if possible.

Thanks in advance,
Anthony.

 

[Arvin Meyer]

Paste this function into a standard module and call it in VBA or in an
Access query to update the column, or create a new column and update that
one:

Function ParseNumber(strIn) As String
'-----------------------------------------------------------
' Arvin Meyer 8/13/1995
'-----------------------------------------------------------
On Error Resume Next

Dim strTmp As String
Dim lngPtr As Long 'pointer

For lngPtr = 1 To Len(strIn)
If IsNumeric(Mid(strIn, lngPtr, 1)) Then
strTmp = strTmp & Mid(strIn, lngPtr, 1)
End If
Next lngPtr

ParseNumber = strTmp

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Anthony]

GREAT, Works perfectly, Thanks
Is it also possible to fill in a default value when there are digits found.
e.g.. If the field has only text characters and no numbers in the string,
could
there be a default value of "1" ??

Thanks again,
Anthony

 

[Mike Painter]

GREAT, Works perfectly, Thanks
Is it also possible to fill in a default value when there are digits
found. e.g.. If the field has only text characters and no numbers in
the string, could
there be a default value of "1" ??

Thanks again,
Anthony

Yes. See what I've added.
Note also that " somevalue 12 apt 3" will result in the number 123 being
the result of the routine.


StrTmp = "" 'to be safe
If strTmp = "" then
ParseNumber = 1
else

 

[Anthony]

Super, Great,
This is perfect, Thanks for all help,

Greets,

Anthony