Excel Guru's check in please

Factual Questions
1

OK,

I’ve got a very simple worksheet. I have one column that contains words. I’d like to figure out a way to create a function (macro, whatever) that builds a comma seperated string of each word in that first column. Then if I add, or remove a word from that first column, the cell that contains that single string is updated automatically. Make sense?

Its like 200 rows of words so its not really ideal to manually write in =", “&$A$1&$B$1&”, “&…” for them all, if you get my drift.

Thanks Peeps.

2

Easiest way is to put a series of formulas in an adjacent column, like so.
Assuming your words are in a1:a200, in b1 enter “=a1”,
in b2 enter “=b1&”,"&a2
copy that entry down as far as you need to go

3

This could probably be done with a macro but if you just want to avoid all the typing, here’s a kludgy kind of way. Assuming your values are in A1:A200, put the formula =concatenate(a1," “,a2,” “,a3,” “,a4,” ",a5) in B1. Then copy that into B6, B11, B16, etc thru B196. Then put the formula =concatenate(B1," “,B6,” “,B11,” “,B16,” ",B21) in C1 and so on until you have a single formula combining many others.

4

If you want to use a formula, here’s how. It’s only mildly more complex than the above excellent suggestions, but will not clutter your sheet with unneeded excess.

Start Excel.
Press ALT+F11 to start the Visual Basic Editor
On the Insert Menu, Choose Module
In the module, enter the following code


Function List_With_Commas(Cells_To_Do As Object)
    For Each cell In Cells_To_Do
        If IsEmpty(cell.Value) = True Then Exit For
        Result = Result & cell.Value & ", "
    Next
    Result = Left(Result, Len(Result) - 2)
    List_With_Commas = Result
End Function

Press ALT+F11 to return to Excel
Put the terms you like in a column, say column A
In whatever cell, say B2 enter


=List_With_Commas(A:A)

Voila!

Note that instead of A:A, you can plug in 1:1 (to do a line instead of a column), A1:A10 (to cover only those cells), or A:C (to cover multiple columns)

Disclaimers

  • This code looks at an empty cell as the end of the list, so if you have entries 1-35, and you delete 12, the code will think you have 11 entries.
  • Note the funky thing I did to make it so there isn’t a trailing comma. There is no doubt a cleaner way.
5

No no no no no! It isn’t that complicated!!
if cell A1 is the cell with all the words in it, here’s your formula:

=SUBSTITUTE(A1," “,”,")

6

Copy the column with the words to a new, empty worksheet.

Do a “Save As” and select the “.txt” format.

Import into any text editor or word processor and perform a search and replace. Replace every instance of CR/LF (or the paragraph mark in Word) with the string ", " and save the file with a “.csv” extension.

Now open the new .csv file in Excel. Voila… cell A1 will have your comma seperated list.

Except that ISTR a 256 character limit for the contents of a cell. I don’t remember the size, but I certainly did hit it once writing the gnarliest nested IF statement you’ve ever seen. ::shudder::

7

Bill H., thanks. That is EXACTLY the solution I was looking for.

keno, in my impatience I used your method and it worked just fine, but like Bill mentioned it is pretty kludgey in that it makes for a pretty ugly sheet. Nevertheless thanks for the help, it was effective.

aramis, your method looks basically the same as kenos but is a bit more effort.

bughunter, I was shooting for a method that was maintainable, that I could edit freely and have a nice clean string to use right away. No doubt that you’re method would have worked, but from a repetitive standpoint it wasn’t much use.

All that aside, thanks very much to everyone who chimed in their advice. Its greatly appreciated.