asked on

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.

zorvek (Kevin Jones)

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.

Kevin

zorvek (Kevin Jones)

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?

Kevin

cjseipp

ASKER

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,

charlie

zorvek (Kevin Jones)

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

Kevin

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cjseipp

ASKER

YEAH YES INDEED..........I'll never let my subscription to the site lapse again

Importing a &quot;.txt&quot; file's comma separated e-mail addresses into Excel

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