Extract numeric values from string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an address field

ADD
P.O. BOX 123
89 Eigth Stree
PO BOX 567

How can I extract only the numeric values from this field into a second field? Is there an Access Function for this
I used a combination of functions to achieve some results (Left, Instr, Len) but there must be a better way to accomplish this

Thanks
jp
 
jp said:
I have an address field:

ADDR
P.O. BOX 1234
89 Eigth Street
PO BOX 5677

How can I extract only the numeric values from this field into a second field? Is there an Access Function for this?
I used a combination of functions to achieve some results (Left, Instr, Len) but there must be a better way to accomplish this.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't think there is a very elegant way to accomplish this. I'd
probably try a brute-force method that is easy to understand:

Function GetNumbers(strValue As String) As String
' Extract the numeric characters from the input string

Dim i As Integer
Dim c As String
Dim strResult As String

For i = 1 to Len(strValue)
c = Mid$(strValue, i, 1)
If IsNumeric(c) Then strResult = strResult & c
Next i

GetNumbers = strResult

End Function

Test: strAddress contains your example address

strNumbers = GetNumbers(strAddress)

strNumbers would contain "1234895677"


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQE+PAoechKqOuFEgEQLVhACg8fWkv1duGZySHAyIvD2Onx1DT50AoN2O
ebq4jHVK63EKkFd5OYZpPHpT
=tvGB
-----END PGP SIGNATURE-----
 
Back
Top