I have a client who has an Excel spreadsheet with one column of names.
Each cell in that column is in the format:

LASTNAME, FIRSTNAME

He wants to take that column of entries and convert each to:



.... where XYZ.com is the same for each entry.

Is there an Excel macro that will do that? Or a VB script?

TIA

You can do it either with a formula or with VBA code. Insert a new column
next to your existing names and enter the following formula and fill down as
far as you need to go.

=TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&"."&LEFT(A1, "

If you want a VBA approach, use the following macro:


Sub CreateAddresses()
Dim LastName As String
Dim FirstName As String
Dim Domain As String
Dim Pos As String
Dim R As Range

Domain = "xyz.com" '<<<< CHANGE

For Each R In Selection.Cells
Pos = InStr(1, R.Text, ",", vbBinaryCompare)
If Pos 0 Then
LastName = Left(R.Text, Pos - 1)
FirstName = Trim(Mid(R.Text, Pos + 1))
R(1, 2).Value = FirstName & "." & LastName & "@" & Domain
End If
Next R

End Sub

Select the cells to change and then run the code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


wrote in message
oups.com...
I have a client who has an Excel spreadsheet with one column of names.
Each cell in that column is in the format:

LASTNAME, FIRSTNAME

He wants to take that column of entries and convert each to:



... where XYZ.com is the same for each entry.

Is there an Excel macro that will do that? Or a VB script?

TIA

One way:

=TRIM(MID(A1,FIND(",",A1)+1,255) & "." &
TRIM(LEFT(A1,FIND(",",A1)-1)) & ")


In article .com,
wrote:

I have a client who has an Excel spreadsheet with one column of names.
Each cell in that column is in the format:

LASTNAME, FIRSTNAME

He wants to take that column of entries and convert each to:



... where XYZ.com is the same for each entry.

Is there an Excel macro that will do that? Or a VB script?

TIA

On Wednesday, October 24, 2007 1:43:34 PM UTC-4, Chip Pearson wrote:

This was super helpful for me. Thanks.