By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,487 Members | 1,747 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,487 IT Pros & Developers. It's quick & easy.

How to delete unwanted characters from an address field

P: 48
i have a table which contains an address field. i want to delete the zeros"0" "@" at the beginning of the address and i.e if i have "00312 @elm st" i want the result to be "312 elm st" here is my code. i call this function in my query but the result field is the same as the original field. the database is attached in the "out range error" thread
Expand|Select|Wrap|Line Numbers
  1. Public Function StripDup(ByVal varWith As Variant) As String
  2.     Dim intX As Integer, intY, intZ As Integer
  3.     Dim strWith As String
  4.  
  5.     If IsNull(varWith) Then
  6.         StripDup = ""
  7.         Exit Function
  8.     End If
  9.  
  10.     StripDup = Trim(varWith)
  11.     varWith = Split(StripDup, " ")
  12.     For intX = 0 To UBound(varWith) Step 1
  13.         If Left(varWith(intX), 1) = "@" Then
  14.             varWith(intX) = LTrim(varWith(intX))
  15.         End If
  16.     Next intX
  17.  
  18.     For intY = 0 To Len(varWith(0)) Step 1
  19.         If Left(varWith(0), 1) <> "0" Then Exit For Else varWith(0) = LTrim(varWith(0))
  20.     Next intY
  21.     StripDup = ""
  22.     For intZ = 0 To UBound(varWith)
  23.         StripDup = StripDup & " " & varWith(intZ)
  24.     Next intZ
  25.     StripDup = LTrim(StripDup)
  26. End Function
Dec 7 '11 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,679
I haven't extensively tested this Code butu it should Remove all Leading '0s' and extract any '@s' from the String:
  1. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fStripCharacters(strStringToStrip As String) As String
    2. Dim intCtr As Integer
    3. Dim blnFoundZero As Boolean
    4.  
    5. 'No Leading Zero(s)
    6. If Left$(strStringToStrip, 1) <> "0" Then
    7.   fStripCharacters = Replace(strStringToStrip, "@", "")
    8.     Exit Function
    9. End If
    10.  
    11. 'At least 1 Leading Zero, strip Leading Zeros only & return remainder
    12. For intCtr = 1 To Len(strStringToStrip)
    13.   If Mid$(strStringToStrip, intCtr, 1) <> "0" Then
    14.     'blnFoundZero = (Mid$(strStringToStrip, intCtr) = "0")
    15.       'If Not blnFoundZero Then fStripCharacters = Replace(Mid$(strStringToStrip, intCtr), "@", "")
    16.       fStripCharacters = Replace(Mid$(strStringToStrip, intCtr), "@", "")
    17.         Exit For
    18.   End If
    19. Next
    20. End Function
  2. Sample Calls to Function:
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print fStripCharacters("000014090 @E@l@m Str@eet")
    2. Debug.Print fStripCharacters("00312 @Elm @st@")
    3. Debug.Print fStripCharacters("00000000000000000000000000 Lotta Zeros!")
    4. Debug.Print fStripCharacters("@@@@@@@@@@@@@@@@@@@@@@@@@ Lotta @'s")
    5. Debug.Print fStripCharacters("1313 @Mockingbird Lane@")
    6. Debug.Print fStripCharacters("@@@@@@@@@@@@@@@@@@@@@@@@@")
    7. Debug.Print fStripCharacters("00000000000000000000000000000000000")
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. 14090 Elm Street
    2. 312 Elm st
    3.  Lotta Zeros!
    4.  Lotta 's
    5. 1313 Mockingbird Lane
    6. [Empty String]
  4. P.S. - There is probably an easier way to do this, but it eludes me at the moment, and I am pressed for time.
Dec 8 '11 #2

P: 48
WOW, worked on the first try. thanks a lot this site really rocks.
Dec 8 '11 #3

ADezii
Expert 5K+
P: 8,679
@almaroc:
Glad it all worked out for you.
Dec 8 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.