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:

(E-Mail Removed)

.... 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,FIND(",",A1)-1)&"@xyz.com"

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)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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:
>
> (E-Mail Removed)
>
> ... 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)) & "@XYZ.com")


In article <(E-Mail Removed)>,
(E-Mail Removed) 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:
>
> (E-Mail Removed)
>
> ... where XYZ.com is the same for each entry.
>
> Is there an Excel macro that will do that? Or a VB script?
>
> TIA