Change Names to E-Mail addresses Q

D

Don Guillett

This macro should do it

Sub makeemailaddress()
For Each c In Range("a3:a5")
With c
.replace "'", ""
.Value = LCase(c.Value)
x = InStr(c, " ")
.Value = Left(c, x - 1) & "." _
& Right(c, Len(c) - x) & "@abc.com"
End With
Next
End Sub
 
R

Rick Rothstein

For the text examples you showed, this will work...

=SUBSTITUTE(A1," ",".")&"@abc.com"

However, you don't say how your worksheet handles multiple first or last
name. I have a two friends whose names show the problem...

Mary Ann Jones << Mary Ann is her first name

Robert Della Rossa << Della Rossa is his last name

They are not married, but if they where, her name would be...

Mary Ann Della Rossa

The formula I gave you will not handle these types of names. And, if the
names are in the same cell (as my formula assumes you meant), I don't think
there is any way to handle names like these.
 
D

Don Guillett

To convert to hyperlinks, change below to add the line

end with
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=c
next
 
D

Don Guillett

A formula solution.
=SUBSTITUTE(SUBSTITUTE(D3," ","."),"'",""))&"@abc.com"
to make a hyperlink
=HYPERLINK(LOWER(SUBSTITUTE(SUBSTITUTE(D3," ","."),"'",""))&"@abc.com")
 
Top