Removing non-numeric values from a string

  • Thread starter Thread starter Luke Bellamy
  • Start date Start date
L

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
 
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
 
Back
Top