Removing non-numeric values from a string

[Luke Bellamy]

Hi,
I am importing data into my Access 2002 application from Excel
Spreadsheets
using VBA (automation etc). I have found that some of the values may appear
like this "$1,000.89cr" so need to remove the chars before applying the
value
to the numerical field. I have written a routine that checks this string and
if it
is non-numeric and not the dollar sign, commas, full-stops then it removes
it.

I can see an issue here with internationalisation (i.e If the pound sign was
used)
so thought I'd ask if anyone know a better routine within Access to do
something
like this?

Thankyou

 

[Arvin Meyer]

Kip Woodward wrote the following function, which together with the Val()
function will give you exactly what you want. Use it like:

=Val(Strip("$1,000.89cr", "[ ,-]", True))

Public Function Strip(vntText, strWhite As String, Optional
blnStripLeadingZeros As Boolean)
'Usage: Strip(TextString, WhiteSpace, StripZeros)
'
'TextString: The string you want to clean up. It could be a literal, or a
column in a query.
'WhiteSpace: A string following the Access wildcard conventions.
' You would want to use "[ ,-]" to eliminate spaces,
comma's and dashes.
' I believe the dash has to come last since it also means
thru in wild cards.
'StripZeros: Use true if you want to eliminate LEADING zeros. My logic says
if a zero
' starts the string, or follows WhiteSpace before
non-whitespace, then drop it.


Dim x As Integer
Dim lenText As Integer

On Error GoTo ErrorThis

lenText = Len(vntText)

For x = 1 To lenText
If Not Mid(vntText, x, 1) Like strWhite Then Strip = Strip &
Mid(vntText, x, 1)
If blnStripLeadingZeros And Mid(vntText, x, 1) = "0" Then
If x = 1 Then
Strip = ""
Else
If Mid(vntText, x - 1, 1) Like strWhite And Not Mid(vntText,
x + 1, 1) Like strWhite Then
Strip = Left(Strip, Len(Strip) - 1)
End If
End If
End If
Next

ExitThis:
Exit Function

ErrorThis:
MsgBox "Error number " & Err.Number & ": " & Err.Description

GoTo ExitThis

End Function

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