I tried to figure this out on another forum and got no where. Can anyone help.
I have a list of emails I need to merge into one cell and separate by commas. It looks like the follwing
A
1. xxx@xx.com
2. xxxx@x.com
3. x@x6.com
I need it to look like
A
1. xxx@xx.com, xxxx@x.com, x@x6.com - Note - there are 8700 emails
Hi xburbx,
Excel has a limitation on the amount of text you can put in a cell. For Excel 2007 and Excel 2003 that limit is 32,767 characters (and even then, only 1,024 will actually show in the cell).
If you have 8700 entries, they'd have to all be about 3 characters long for you to fit them in one cell. Will multiple cells work? With an average e-mail address length of 20+ characters, you're going to need to use a handful of cells (at least 6 if they were all only 20 characters).
Yea, 6 cells are fine. Just need to do large bulk copy and pastes into a txt file, so 6 is fine.
Instead of using multiple cells and then having to copy/paste into a text file, how about simply creating the text file itself?
The code below will loop through the cells in column A (from A1 to the last used row in column A) and output each cell's text into a text file (.txt). All of the entries will be separated by commas.
Change the value of 'DestFile' to the path and file name you desire.
Sub CommaExport() Dim DestFile As String, FileNum As Long, RowCount As Long DestFile = "C:\emails.txt" FileNum = FreeFile() On Error Resume Next Open DestFile For Output As #FileNum If Err <> 0 Then MsgBox "Cannot open filename " & DestFile End End If On Error GoTo 0 For RowCount = 1 To Range("A65536").End(xlUp).Row Print #FileNum, Cells(RowCount, 1).Text & ", "; Next RowCount Close #FileNum End Sub
Last edited by Paul; 01-25-2008 at 12:58 AM.
Probably a dumb questions, but where do I put that code?
nm, figured it out and it worked. thanks a ton
You'll want to drop this into a macro. If you've never played with these before it can seem intimidating, but really all they are is a recorder that tracks your steps so you can repeat them later.
Easiest way to take care of the code is click on Tools, Macro, Record New Macro. You'll get a dialog box asking for the name. Type in something simple like NewCode. After you click "Ok" you'll see a little box with what appears to be a square 'Stop' button. Click on this to stop recording the macro.
Now go back and click on Tools, Macro, Macros. You'll have a box showing the names of your macros. Highlight "NewCode" (or whatever you named it) and then click on the "Edit" button. Copy the code given in the previous post and paste it in here.
Just a heads up - you'll see some rows in there that automatically start with a single quote - these rows are commented out, meaning that anything on a row after the single quote will not be executed when you run your macro. So when you paste in your code just make sure you avoid those rows.
Once your code is pasted click on the icon to save the book and then you can close the code editing box.
With your macro saved you're going to go back to Tools, Macros, highlight your "NewCode" macro, and then hit "Run" and you should be in business.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
xburbx 
Register To Reply
Bookmarks