Copy of just numerical values within strings

[BusyProfessorFromBremen]

Hey @ all,

as I elaborated on a table I was revealing an issue by handling one column.
In this column there are values prevalent which aren't in a proper format.
They contain both numerical as well as alphanumerical values. Thus I would
like to copy just the numerical values out of these boxes. From there on I
can observe the information more in detail than I can do now. To simplify the
problem I give a brief example:

State of the art:

ID VALUE

123 45-32HH
123 22-33
321 24-1235GL
321 49-33HH

It should look like:

ID VALUE VALUENEW

123 J45-32HH 45-32
123 D22-33 22-33
321 24-1235GL 24-1235
321 49-33HH 49-33

Would be great if anybody has got a clou how to solve this problem.Thank you
very much in advance!

malte

 

[Daniel Pineault]

You'd have to use a custom function like:

Public Function StripAllChars(strString As String) As String
'Return only numeric values from a string
Dim lngCtr As Long
Dim intChar As Integer

For lngCtr = 1 To Len(strString)
intChar = Asc(Mid(strString, lngCtr, 1))
If intChar >= 48 And intChar <= 57 Then
StripAllChars = StripAllChars & Chr(intChar)
End If
Next lngCtr
End Function

You'll need to add a check/exception for the '-' as you want to keep that
character. Or you could use it as is and then perform an update to add it
back in since it seem to always be the 3rd character in the sequence.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.