Strip e-mail from cell

[Keeper4826]

I have a spreadsheet which has a "User Description" column. In this column, most users have data that looks similar to this:

APPLICATION - john.smith@mycompany.com
APPWEB - jane-doe@somecompany.com
APPWEB - john_doe@othercompany.com
APPLICATION - TP - paul@acompany.com
APP/APPWeb/DEV - paulsm@thecompany.com

There are also cells in this column which literally have a sentence long description, but those are not my focus. What I need to do is find a way to strip out the e-mail address found in the examples above and copy them into a new cell (the same row for the corresponding user). I don't know where to begin for this. Can anybody help?

 

[Scott Huish]

erik.van.geit:

I've been doing it for a while now. The MOREFUNC Regular Expressions page was where I started with MOREFUNC's regular expression functions, then observing VBA posts regarding it, I bought a book too, but looking things up on the web as I've needed them has proven to be very helpful.

I didn't use it as a sheet function because the op didn't want to copy it to all the other cells.

 

[Keeper4826]

Hotpepper, you too??
We saw posting jindon, then Richard some of this "regular expressions" stuff. Now you too! Still Chinese for me... Where did you learn it?

Why not use it as a sheetfunction?
Code removed

We can notice that your function also extracts emailaddresses from the "middle" of the string as you can see in B7:B11.

Yesterday I posted this function, but it seems that it didn't get through
Code removed

best regards,
Erik

No offense inteded Erik. I'm just not all that familiar with macros, so I wasn't sure where to, or rather how to, apply your code. I'm going to keep playing with these suggestions to better learn the program.

 

[erik.van.geit]

Keeper4826, I didn't feel offended

Hotpepper, thank you for the reponse: once I'll understand the syntax ...

 

[jindon]

If the email can be in the middle of the string
   A                                          B                          
 7 APPLICATION - john.smith@mycompany.com +++ john.smith@mycompany.com   
 8 APPWEB - jane-doe@somecompany.com other ++ jane-doe@somecompany.com   
 9 APPWEB - john_doe@othercompany.com more    john_doe@othercompany.com  
10 APPLICATION - TP - paul@acompany.com etc   paul@acompany.com          
11 APP/APPWeb/DEV - paulsm@thecompany.com     paulsm@thecompany.com      

Sheet1
[Table-It] version 07 by Erik Van Geit

Hi
Just for a fun

Function erik(txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "^[a-z0-9][a-z0-9_\.\-]+@[a-z0-9\-\.]+(\.[a-z]{2,3})+$"
    .IgnoreCase = True
    erik = .execute(txt)(0)
End With
End Function

Some domain includes like .co.uk, .co.jp, .ne.jp etc...

 

[erik.van.geit]

OK, let the fun go on
could you check that again, jindon?
seems to return #VALUE! for all the given examples
I tried changing the code to no avail. Even replacing "erik" by "jindon" didn't help

have a nice day,
Erik

 

[Scott Huish]

I think this would fix it:

Function erik(txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "[a-z0-9][a-z0-9_\.\-]+@[a-z0-9\-\.]+(\.[a-z]{2,3})+"
    .IgnoreCase = True
    erik = .Execute(txt)(0)
End With
End Function

 

[erik.van.geit]

Yes, hotpepper, that works for me too.
If someone is interested to avoid #value

Function jindon(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[a-z0-9][a-z0-9_\.\-]+@[a-z0-9\-\.]+(\.[a-z]{2,3})+"
        .IgnoreCase = True
        On Error Resume Next
        jindon = .Execute(txt)(0)
        'If jindon = vbNullString Then jindon = "no valid email"
    End With
End Function

have a nice weekend!
Erik

EDIT: corrected codetags

 

[Scott Huish]

No need for On Error you just need to test the string first:

Function erik(txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "[a-z0-9][a-z0-9_\.\-]+@[a-z0-9\-\.]+(\.[a-z]{2,3})+"
    .IgnoreCase = True
    If .test(txt) Then erik = .Execute(txt)(0)
End With
End Function

 

[erik.van.geit]

you are learning me some stuff thanks!

Though I think that would be slower, assuming that the "test" and "execute" take the same amount of time... I'll test a bit now...

EDIT: didn't test with chrono, not sure, but I do not want to go to the bottom for this I'll leave it to someone else if he wants to test

 

[jindon]

Oops

Thanks HOTPEPPER, "$" shoudn't be there...