Combining text from several fields into one

alivibe

New Member
Joined
Mar 7, 2004
Messages
3
Hi. I have a list of email addresses in Excel 7.0a that I need to format in order to use them in my email program. The addresses appear in a column like so:

mike@yahoo.com
ted@sbcglobal.net
aaron@comcast.net

I need to combine these emai addresses so they have semi-colons and one space separating them, so that they read:

mike@yahoo.com; ted@sbcglobal.net; aaron@comcast.net

Then I'll cut and paste them into my email message.

What is the best way to do this? Thank you so much for your help!

~Ali
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Hi - Welcome to the board

Assuming these emails are in column A then

in another cell type

= A1 & "; " & A2 & "; " & A3
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
=A1&"; "&A2&"; "&A3

Assuming that your original list is in A1:A3.

HTH

Mike
 

alivibe

New Member
Joined
Mar 7, 2004
Messages
3
Thanks--but I have a dumb question. How do I actually see the email addresses? All I see now is #NAME? in the cell I typed the formula.

Also, I have to do this with thousands of email addresses. Is there an effective way to do this for 1,000 email addresses at a time?

Thanks so much for your help!!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Welcome to the Board!
Book1
ABCD
1E-MailAddressesConcatenatedAddresses
2mike@yahoo.commike@yahoo.com;ted@sbcglobal.net;aaron@comcast.net
3ted@sbcglobal.net
4aaron@comcast.net
Sheet1


As for 1,000 addresses, concatenating is probably the wrong way to go and you'll also exceed the Cell Text limit - 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

What e-mail client are you using? You can name your range of addresses and set Recipient = MyEmailRange.

Hope that helps,

Smitty

EDIT: Crappy spelllinjg :wink:
 

alivibe

New Member
Joined
Mar 7, 2004
Messages
3
I'm using web based email--I can use either Hoarde, NeoMail or SquirrelMail. What I originally wanted to do was copy and paste 1,000 emails at a time into the BCC: field, but when I tried to do so, it only copied the first address (I think because the addresses are separated by paragraphs rather than spaces).

I can copy and paste a bunch of addresses into my Outlook Express, but I have a limit on how many emails I can send out that's pretty low.

I've been trying to get this email out for weeks now--it's been a real nightmare... :oops:
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

If using Outlook/Outlook Express is an option, see: http://www.rondebruin.nl/

He's got pretty much all the code that you'll need as well as 2 free Add-Ins.

Smitty
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Try this quicky vba macro

Code:
Option Explicit

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub CombineEmails()

Dim x               As Integer
Dim LastRow         As Integer
Dim MyEmails        As String
Dim FileToOpen      As String
Dim lngRet          As Long

    FileToOpen = ThisWorkbook.Path & "\" & "EmailList.txt "
    LastRow = Range("A65536").End(xlUp).Row
    For x = 2 To LastRow
        MyEmails = MyEmails & "; " & Range("A" & x).Value
    Next x
    MyEmails = Mid(MyEmails, 3, Len(MyEmails))
    Open ThisWorkbook.Path & "\EmailList.txt" For Append As #1
    Print #1, MyEmails
    Close #1
    lngRet = ShellExecute(0, "Open", FileToOpen & vbNullString, vbNullString, vbNullString, 1)

End Sub

Now just copy and paste from notepad to your email program.
 

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
Great Site Pennysaver bro .

I was actually searching it for some few fonths to be honest
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Of course I assumed that your emails were in Column A starting with Row 2. Change as needed.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,452
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top