Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trim .com from string (Access 2003)

    I've inherited a table that has thousands of records that require some trimming. Some entries have extra characters after the email domain ".com" or ".net". It appears to be a pretty simple modification but it's been along time for me. Can somone please point me to a post that handles such a script?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim .com from string (Access 2003)

    If you want to remove .com:
    Open the table.
    Click in the relevant field.
    Select Edit | Replace...
    Enter .com in the Find what box, and leave the Replace with box empty.
    Select Part of Field from the Where dropdown list.
    Click Replace All.

    Similar for .net.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim .com from string (Access 2003)

    I wish it were that simple. I guess I didn't explain it properly. I don't want to remove the ".com" piece from the string. I want to Right trim the string and remove all characters after the .com or .net

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim .com from string (Access 2003)

    YIKES...I just found a huge problem. This does nothing to catch the country variants: ".co.uk" et al. This just got 10 times more complicated.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim .com from string (Access 2003)

    Use this function as part of an update query. It will strip everything to the right of the first "." it finds after the first "@" it finds. If there is no "@" or "." after a "@" the original text is kept unchanged

    <pre>Function StripIt(theAddress As String) As String
    Dim intAt As Integer
    intAt = InStr(theAddress, "@")

    If intAt = 0 Then
    StripIt = theAddress
    Exit Function
    End If

    If InStr(intAt, theAddress, ".") = 0 Then
    StripIt = theAddress

    Else
    StripIt = Left(theAddress, InStr(intAt, theAddress, ".") - 1)
    End If
    End Function
    </pre>


  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trim .com from string (Access 2003)

    Hi Dj

    I have made a number of assumptions but we can manipulate the code for ant exceptions you may find.

    The code "assumes" that there is only one type of email addess that ends in UK and seearches for the position of the last "." in the email address, this iagain assumes that the extra characters that you are finding do not have that character in it. Have a look and see if there any problems ( there are bound to be a few)

    Function strip(mail As String)
    Dim intLength As Integer



    If Mid(mail, InStrRev(mail, ".") + 1, 2) = "uk" Then

    intLength = InStrRev(mail, ".") + 2

    strip = Left(mail, intLength)

    Else

    intLength = InStrRev(mail, ".") + 3

    strip = Left(mail, intLength)

    End If


    End Function
    Jerry

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim .com from string (Access 2003)

    Thanks for your help, All

    I've done the quickest thing...which is place it in Excel
    Insert the Stripit Function (Which was modified to a +3 on the right side of the trim)
    Then called that from another column (= Stripit (a1))
    This handled most of the nastiness

    The 'non-compliant' ones were handled by filter and replace
    Filter = Contains ".co.uk" {et al.}
    Then I just copied and pasted and manually edited the dozen or so entries. No biggie

    Inelegant...but it's done!
    Thanks again.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trim .com from string (Access 2003)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    Needs must when the Devil drives <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>sometimes the best thing to do if it is a one off
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •