Building a comma delimited string from Cell Range

I would like to build a comma-delimited string from a range of cells. I would like to be able to put a number in the cell next to the word to indicate whether or not to include the cell in the string. Once there is a number entered in column b, I would like to sort on column b.

For example,

word1 1
word2 2
word3 3
word4

I need a function to build a string to look like "word1,word2,word3"

Thanks,

g

Please Login or Register  to view this content.

That function only goes three deep. C Pearson has created a whole "addin" that turns the mildly effective Concatenate function into a fully usable StringCat function. Use this page to create the addin and use it, simple stuff based on your example.

http://www.cpearson.com/Excel/StringConcatenation.aspx

sglife, thanks for your example. It might be easier since I don't know how to put functions in excel. Also, one thing that I've noticed is if I have multiple cells with the same number in the order field, it only includes the first one with that number. For Example, several words marked as order 2, it only includes the first one.

Example:

poker guide 1
poker strategy 2
poker help 2
poker guides 2
holdem guide 3
poker manual 3


JBeaucaire, that function looks pretty powerful but I don't know how to put UDFs in a workbook. Can you explain or point in the right direction?

g

Originally Posted by Patchworks

JBeaucaire, that function looks pretty powerful but I don't know how to put UDFs in a workbook. Can you explain or point in the right direction?

1. Open up the workbook
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function examples - (I've copied the text of the UDF for you below)
5. Get out of VBA (Press Alt+Q)
6. Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins...). Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet.

Please Login or Register  to view this content.

Originally Posted by Patchworks

Ok, I've done exactly as you said, but there is where I get evertime. WHen I try to use the UDF I get #NAME? in the cell. I cant find the UDF!

g

The last instruction above tells you where to find them:

Select a cell, press Shift+F3, the Paste Options appear, under "select a category" choose "User Defined" (near the bottom of the picklist)

Examples Of StringConcat

The following are a few examples of the StringConcat function.

Concatenating literal text:

Please Login or Register  to view this content.

returns A|B|C

Concatenating text in a range of cells:

Please Login or Register  to view this content.

returns the values from B1:B5, each separated by a | character.

Concatenation In An Array Formula

Please Login or Register  to view this content.

returns the values from cells C30:C39, separated by a | character where the corresponding value in the range B30:B39 is greater than 4.

And your original array:

Please Login or Register  to view this content.

Makes word1,word2,word3 from Column A if there is a number in Column B.

Works perfect. That is an ARRAY formula. You have to press CTRL-SHIFT-ENTER to make Excel apply the array, just ENTER doesn't turn on the array. Try again.

As JB says, it's an array formula, but not because of StringConcat; it's because of this part:

=StringConcat(",", IF(B5:B9>0, A5:A9, "") )

... because the IF has to evaluate an array and return an array.

Excel is a complex but totally repeatable application. It rewards the time invested in understanding.

They no longer publish a bridge column in the Dallas Morning News. That's a shame.

Originally Posted by Patchworks

Guys, this array thing is way to quirky. Sometimes it works, sometimes it doesn't. 6 months from now I will never remember to turn the left, hold my breath and jump 3 times on one leg.

Is this the only way I'm gonna get this done? I've spend 2 weeks with countless hours trying to get a frickin list of words and this just isn't working?

Is there another way?

I don't mean to "speechify" but everything in life is hard until you know how. Everything. Learning to press CTRL-SHIFT-ENTER is a new idea, granted. Very new. But the idea you can't get used to it, or remember later, I don't accept that. I think you can.

Is this the only way to do this? No, you can spend countless hours learning how to program in VBA and maintain a macro of some extensive complication. Does that sound EASIER? No.

You've done work, installed the UDF, learned how it works. You're only a CTRL-SHIFT-ENTER away from being done. You can do it.