Importing a ".txt" file's comma separated e-mail addresses into Excel

Here is what I have.

A ".txt" file that contains valid e-mail addresses seperated by commas.

This was created by copying a collection of 200+ e-mail addresses that were seperated by commas. Then I pasted them into  a blank Notepad document. Then saved the file with the default ".txt" extension.

Here is what I wish to accomplish:
Take the text file containing the data and produce a file that could be used to import the e-mail addresses into Outlook,

Outlook Express, Eudora or any other e-mail program the client is using.

Here is what I have tried:

Opened a blank worksheet in Excel. Created a header in cell A1 named "email address". Pasted the list of e-mail addresses from the Text file into cell A2. Save as a csv file. This Excel CSV file when imported produces a single contact with all addresses that were pasted into cell A2.........

Here is what I believe needs to occur:

Importing/Inserting/Copying the list of e-mail addresses into individual cells in Column A(with cell A2 reserved for the Field Name "email address")

Can this be automated via a Script, Macro etc? I am a novitiate with these techniques, but will pursue those paths if necessary.

Thanks for the help,

I have enjoyed your site's support and knowledge base.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Probably the easiest approach in this case is to open in Word (or any other text editor) and replace the commas with carriage returns. Then you can import into anything you want, including Outlook.

zorvek (Kevin Jones)ConsultantCommented:
Or is this really a list of "e-mail addresses seperated by commas"? Is it a list of contacts that happen to include an email address where each contact is on it's own line and separated by commas?

cjseippAuthor Commented:
Ok I have tried your technique.

I opened a blank Word document. pasted in a string of about 20 comma seperated e-mail addresses, removed the commas(at which time Word recognized them as e-mail addresses, underlined them and colored them blue), I saved the file as a Word document.

I opened Outlook, went to Import from another Program or File. However there is no option to Import data from a Word document.

So I need to somehow Export this information from Word into a viable format for Outlook to accept.

Furthermore, I will need to be doing this many many times. Possibly thousands of e-mail addresses.

Thus the need automate the process of moving the data from the text file into Excel.

Thanks Again,

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

zorvek (Kevin Jones)ConsultantCommented:
Here is a macro that will split a string of tokens (email addresses) stored in a single cell and separated by commas. To use, put the list of email addresses into a single cell as you have done in the past, select that cell, and run the macro. The macro places the resulting list in the cells below the selected cell so make sure there is nothing below that you want to keep.

Public Sub SplitTokens()

   Dim Values As Variant
   Values = Split(Replace(Selection.Cells(1, 1).Value, " ", ""), ",")
   Selection.Cells(1.1).Offset(1).Resize(UBound(Values) + 1).Value = Application.Transpose(Values)

End Sub

zorvek (Kevin Jones)ConsultantCommented:
This version of the macro will place the results starting in the selected cell:

Public Sub SplitTokens()

   Dim Values As Variant
   Values = Split(Replace(Selection.Cells(1, 1).Value, " ", ""), ",")
   Selection.Cells(1.1).Resize(UBound(Values) + 1).Value = Application.Transpose(Values)

End Sub


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cjseippAuthor Commented:
YEAH YES INDEED..........I'll never let my subscription to the site lapse again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.