Very wierd situation with my string variable?

[dcharland]

I have this situation where I have an email address put into a variable and I used the trim(mystring) function but my string still looks like this:

"email@address.com "
but if I mouse over on the variable name in break mode I have

"email@address.com"

This is a huge problem as it's not recognized as being equal.

Any ideas?

 

[Michael M]

From what you have posted, the TRIM function has done its job and therefore they would not be equal
You'll note that the trailling space has been removed !
Having said that, what did you want trimmed from the initial string ?

 

[dcharland]

My example is the original post didn't come out properly! Sorry,

The string should be 16 characters long, but there is 8 caracters that are looking to me like a space, but the trim function isn't removing them? This code will be used on various email strings with various situation, so I can't just RTrim the last 8 characters.

This is what I have:

"email@address.com        "

I i'm trying to make it

"email@address.com"

I have a function that cleans the email addresses, but that doesn't take care of it? I thought that the Trim(mystring) should remove those (look like space) characters!! Below is the code for the email string clean:

EmailClean = Replace(EmailClean, "mailto:", "")   ' Strip Mailto:
    EmailClean = Replace(EmailClean, "'", "")         ' Strip single quotes
    EmailClean = Replace(EmailClean, " ", "")         ' Strip empty space
    EmailClean = Replace(EmailClean, "<", "")         ' Strip <
    EmailClean = Replace(EmailClean, ">", "")         ' Strip >
    EmailClean = Replace(EmailClean, "[", "")         ' Strip [
    EmailClean = Replace(EmailClean, "]", "")         ' Strip ]
    EmailClean = Replace(EmailClean, "...", "")       ' Strip ...
    EmailClean = Replace(EmailClean, "Note:", "")     ' Strip Note:
    EmailClean = Replace(EmailClean, "To:", "")       ' Strip To:
    EmailClean = Replace(EmailClean, "Subject:", "")  ' Strip Note:
    EmailClean = Trim(Replace(EmailClean, """", " ")) ' Strip double quotes (replace with a space so it can be split again
    Temp = Split(EmailClean)                     ' Split double email address now separated by a space
    EmailClean = Temp(LBound(Temp))              ' Remember only 1st address
    EmailClean = Trim(EmailClean)                   'Trim the string
    EmailClean = CStr(EmailClean)

 

[xenou]

Often especially on the web you get many odd characters. What you think are spaces are likely something else. You could try a Mid function with an ASC() function to isolate one of these "space" Ascii codes, at least to find out what it is (though I guess it might be Unicode character encoding). It's probably better to try to isolate or pull out the email address rather than remove everything else around it (i.e., by using a regex function).

Don't email have a very limited set of characters allowed? You could probably also (without regex) just find the @ then get the A-Z values before and after it - plus whatever other characters are allowed (dot, hyphen) Do they let underscores in email addresses too?

 

[dcharland]

Hi,

Because the trim function doesn't remove them. Also, Ive notice that this situation seems to happen when the email is the last line in the body of the email.

something like this (assume this is the body of the email)

  This is an automatically generated Delivery Status Notification.
   
  Unable to deliver message to the following recipients, because the message was forwarded more than the maximum allowed times. This could indicate a mail loop.
   
         canadiana@nlc-bnc.ca

They might be (don't know how to properly call them) carriage return's? If they are is there a way to remove them from the string?

Thanks

 

[xenou]

You can strip out carriage returns by replacing line feeds (LF) and carriage returns (CR):

EmailClean = Replace(EmailClean ,Chr(10),"")
EmailClean = Replace(EmailClean ,Chr(13),"")

This might work too (untested, though). I based it on the regex pattern at regular-expressions.info. It should work on any string text you pass in, including multiline strings (but is only written to return the first email address it finds):

Sub Test()
Dim x As String
    
    x = GetEmailAddress("blah blah myname@mysite.com" & vbCrLf & "blah blah" & vbCrLf)
    Debug.Print x

End Sub
'----------------------------------------------------------------------
Function GetEmailAddress(ByRef arg As String) As String
Dim re As Object '//RegExp Object
Dim s As String

    Set re = CreateObject("VBScript.RegExp")
    With re
        .Pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b"
        .IgnoreCase = True
        .MultiLine = True
        .Global = True
    
        If .Test(arg) Then
            s = .Execute(arg)(0)
        End If
    End With
        
    GetEmailAddress = s
    Set re = Nothing

End Function

 

[dcharland]

Thanks to all for your help with this. I've added Xenou recommendations chr(10) and chr(13) to my cleanup script and that fixed it

Denis