Fetching the One email ID from many

[ganeshvenkatram84]

Hi All,
In my project, there is a field in excel sheet which contains one or more email address like,
ABC@yahoo.com/ABCD@gmail.com.
I need to fetch first email ID alone(ABC@yahoo.com). I dont know to to retrieve like that.
Plz help me out since im new to VBA.I need to finish my project early

Ganesh.V

 

[Sektor]

UDF

Function ExtractEmail(str As String) As String
    ExtractEmail = Split(str, "/")(0)
End Function

 

[ganeshvenkatram84]

Hi sektor

Thanks for ur timely reply.Your code works absolutely fine. Many thanks

I have one more doubt
I have used the below code for email validation

Private Sub TxtEmail1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With CreateObject("vbscript.regexp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
If .test(TxtEmail1.Value) Then
MsgBox "OK"
Else
MsgBox "No good"
Cancel = True
End If
End With
End Sub

The code works fine. But in some cases its not working for the following example
Example : <TABLE style="WIDTH: 188pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=250 border=0 x:str><COLGROUP><COL style="WIDTH: 188pt; mso-width-source: userset; mso-width-alt: 9142" width=250><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; WIDTH: 188pt; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" width=250 height=17 x:str="'amrit.boricha@gmail.com.com">amrit.boricha@gmail.com.com
it shows that the above email id is valid
</TD></TR></TBODY></TABLE>
Please Assist <!-- / message -->