Change Names to E-Mail addresses Q
 

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:

An few examples:

Joe Public would be
Joe O'Public would be
Joe McPublic would be
etc etc

Change Names to E-Mail addresses Q
 

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) & "
End With
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Seanie" wrote in message
...
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:

An few examples:

Joe Public would be
Joe O'Public would be
Joe McPublic would be
etc etc

Change Names to E-Mail addresses Q
 

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

=SUBSTITUTE(A1," "

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.

--
Rick (MVP - Excel)


"Seanie" wrote in message
...
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:

An few examples:

Joe Public would be
Joe O'Public would be
Joe McPublic would be
etc etc

Change Names to E-Mail addresses Q
 

To convert to hyperlinks, change below to add the line

end with
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=c
next
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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) & "
End With
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Seanie" wrote in message
...
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:

An few examples:

Joe Public would be
Joe O'Public would be
Joe McPublic would be
etc etc

Change Names to E-Mail addresses Q
 

A formula solution.
=SUBSTITUTE(SUBSTITUTE(D3," "
to make a hyperlink
=HYPERLINK(LOWER(SUBSTITUTE(SUBSTITUTE(D3," ")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Seanie" wrote in message
...
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:

An few examples:

Joe Public would be
Joe O'Public would be
Joe McPublic would be
etc etc