Building Email addresses in Excel...

S

Sean Smith

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:

[email protected]

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

Thanks in advance!

Sean
 
J

Jacob Skaria

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
 
J

JLatham

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
 
J

Jeevan

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
 
J

JLatham

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.
 
J

Jacob Skaria

Please try

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

If this post helps click Yes
 
B

Bill Sharpe

Jeevan said:
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
Click to expand...
....must be a pretty small company. No two first names the same? <g>

Bill
 
Top