Change Names to E-Mail addresses Q

[Seanie]

What formula could I use to convert names to e-mail addresses?

Name would be input in the format: First Last

E-mail address format would be: (e-mail address removed)

An few examples:

Joe Public would be (e-mail address removed)
Joe O'Public would be (e-mail address removed)
Joe McPublic would be (e-mail address removed)
etc etc

 

[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

 

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

 

[Don Guillett]

To convert to hyperlinks, change below to add the line

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

 

[Don Guillett]

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