How can I merge text from 1,000 cells into one cell in Excel 2003?

Question

I know how to use the concatenate function to merge data from separate cells into one, but if I wanted to do 1,000 cells, that would take way long.

I have a spreadsheet of over 1,000 email addresses in column H, and I want to get them all into one cell separated by a comma so I can copy and paste for a mass email invitation for my customers.

Answer 1

You can also do it simply using a macro. The following will create the list in cell I1. To choose a different cell change the I1 in Line 7 to the cell of your choice, i.e. [A1]



Copy the following to the clipboard:



Sub Spl_Concatenate()

Dim i, LastRow, HList

LastRow = Range("H" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

HList = HList & Cells(i, "H").Value & ","

Next

[I1].Value = Left(HList, Len(HList) - 1)

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window opens, highlight the macro and click 'RUN'.

Answer 2

Merging Text Cells In Excel

Answer 3

You would need to create a customised version of the CONCATENATE function, see link below for an example