put this function in a module ,
then run an update query to post the correction in a NEW FIELD. (don't overwrite the original value)
or
run the correction at import if using a query.
usage: FixNum([FIELD])
Code:
Public Function FixNum(ByVal pvNum)
Dim vRet, vChr
Dim iDashCt As Integer, i As Integer
On Error Resume Next
'remove leading zeros
vChr = Left(pvNum, 1)
While vChr = "0"
pvNum = Mid(pvNum, 2)
vChr = Left(pvNum, 1)
Wend
'now keep nums
For i = 1 To Len(pvNum)
vChr = Left(pvNum, 1)
Select Case True
Case vChr = "-"
iDashCt = iDashCt + 1
pvNum = Mid(pvNum, 2)
If iDashCt = 2 Then GoTo endit
Case Asc(vChr) > 47 And Asc(vChr) < 58
vRet = vRet & vChr
pvNum = Mid(pvNum, 2)
End Select
Next
endit:
FixNum = vRet
End Function