Very wierd situation with my string variable?

dcharland

New Member
Joined
Mar 2, 2011
Messages
40
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Mar 2, 2011
Messages
40
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:
HTML:
"email@address.com        "
I i'm trying to make it
HTML:
"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:

HTML:
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)
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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?
 
Last edited:

dcharland

New Member
Joined
Mar 2, 2011
Messages
40

ADVERTISEMENT

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)
HTML:
  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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
You can strip out carriage returns by replacing line feeds (LF) and carriage returns (CR):

Code:
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):
Code:
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

New Member
Joined
Mar 2, 2011
Messages
40
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top