I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

Please try and feedback
A1 = FirstName
B1 = Last Name
C1 = WebAddress

= A1 & "." & B1 & "@" & RIGHT(C1,LEN(C1)-FIND("//",C1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"Sean Smith" wrote:

I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

And if you want to later turn the results of the formula that Jacob gave you
into clickable hotlinks that'll open your email client to send email with,
this VBA code will do that for you - Assumes that the formula is in column D
on the sheet.

To put the code into your workbook, open it up, press [Alt]+[F11] to open
the VB Editor. In the VBE, choose Insert | Module and copy and paste the code
below into that module and close the VBE.

To run it, just use Tools | Macro | Macros and pick it from the list and hit
the [Run] button.

Sub MakeEmailHyperlink()
Dim emailList As Range
Dim emailEntry As Range

Set emailList = ActiveSheet.Range("D1: " & _
ActiveSheet.Range("D" & _
Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
For Each emailEntry In emailList
If Not IsEmpty(emailEntry) Then
ActiveSheet.Hyperlinks.Add _
anchor:=emailEntry, _
Address:="mailto:" & emailEntry.Text
End If
Next
Set emailList = Nothing
End Sub


"Jacob Skaria" wrote:

Please try and feedback
A1 = FirstName
B1 = Last Name
C1 = WebAddress

= A1 & "." & B1 & "@" & RIGHT(C1,LEN(C1)-FIND("//",C1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"Sean Smith" wrote:

I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

Jacob.

Thanks. I tried it and this is the result:

.

I forgot to mention that the Web Address field has:

http://www.wm.com

How can I edit the code to delete the "www"?

Thanks,

Sean

"Jacob Skaria" wrote:

Please try and feedback
A1 = FirstName
B1 = Last Name
C1 = WebAddress

= A1 & "." & B1 & "@" & RIGHT(C1,LEN(C1)-FIND("//",C1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"Sean Smith" wrote:

I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

RIGHT(C1,LEN(C1)-FIND(".",C1))

Change the formula as follows:
= A1 & "." & B1 & "@" & RIGHT(C1,LEN(C1)-FIND(".",C1))

also, take a look at the potentially useful code I posted earlier. You may
find that useful if you want to turn these into links you can click on and
open up your email client to send the person email.

"Sean Smith" wrote:

Jacob.

Thanks. I tried it and this is the result:

.

I forgot to mention that the Web Address field has:

http://www.wm.com

How can I edit the code to delete the "www"?

Thanks,

Sean

"Jacob Skaria" wrote:

Please try and feedback
A1 = FirstName
B1 = Last Name
C1 = WebAddress

= A1 & "." & B1 & "@" & RIGHT(C1,LEN(C1)-FIND("//",C1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"Sean Smith" wrote:

I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

Please try

=A1&"."&B1&"@"&RIGHT(C1,LEN(C1)-FIND("//",C1)-5)

If this post helps click Yes
---------------
Jacob Skaria


"Sean Smith" wrote:

Jacob.

Thanks. I tried it and this is the result:

.

I forgot to mention that the Web Address field has:

http://www.wm.com

How can I edit the code to delete the "www"?

Thanks,

Sean

"Jacob Skaria" wrote:

Please try and feedback
A1 = FirstName
B1 = Last Name
C1 = WebAddress

= A1 & "." & B1 & "@" & RIGHT(C1,LEN(C1)-FIND("//",C1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"Sean Smith" wrote:

I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

=A2&"."&B2&"@"&RIGHT(C2,LEN(C2)-FIND("://",C2)-2)
--
David Biddulph

"Sean Smith" <Sean wrote in message
...
I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

But if the first name and last name are not in separate cells but contained
in one cell, then how do we go about this. In our company, the email address
consists only of first name.

Thanks

Jeevan

"David Biddulph" wrote:

=A2&"."&B2&"@"&RIGHT(C2,LEN(C2)-FIND("://",C2)-2)
--
David Biddulph

"Sean Smith" <Sean wrote in message
...
I have a worksheet that contains the following columns.

First Name, Last Name, Web Address
David, Aardsma, http://wm.com

I am looking for the capability to auto populate an email adress as:



Is there a function that can pull the first name, last name and then just
the text after the http://?

Thanks in advance!

Sean

Jeevan wrote:
But if the first name and last name are not in separate cells but contained
in one cell, then how do we go about this. In our company, the email address
consists only of first name.

Thanks

Jeevan

....must be a pretty small company. No two first names the same? <g

Bill