Trim .com from string (Access 2003)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Needs must when the Devil drives sometimes the best thing to do if it is a one off

Reply | Quote