Concatenate query

[nsutton]

Hello

Is there an easy formula that can concatenate all cells in a column that contain data?

ie D1, D2, D4 and D17 all have data, the others in the column are blank so it returns just four results.

Also is there a way of returning these results in a list format?. ie if the results were apple, pear, orange, lime. I would want them to read like this in a cell

Apple
Pear
Orange
Lime

and not

Apple Pear Orange Lime

hope that makes some sense!.

thanks in advance

 

[hurgadion]

Hi,
try using the Character Chr(10), best regards.

 

[Rick Rothstein]

I don't know if you will be able to do what you want with built-in Excel formulas, but I posted a UDF (user defined function) in this mini-blog article (Flexible Concatenation Function) that, as one of its abilities, will allow you to do what you asked.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConCat just like it was a built-in Excel function. For example, to produce the list you wanted...

=ConCat(CHAR(10),D:D)

 

[nsutton]

hi

You said input the above code in the VB editor, what above code?? do you mean '=ConCat(CHAR(10),D:D)' or am i missing something??

 

[Rick Rothstein]

You said input the above code in the VB editor, what above code?? do you mean '=ConCat(CHAR(10),D:D)' or am i missing something??

Look between the parentheses in the first paragraph... it is a link to the mini-blog article I mentioned immediately before it... click the link and retrieve the code from there and install it according to the directions I gave. You might also want to read the text in the article itself as it will tell you all the functionality available in the ConCat function (which is more than you need for your current request).

 

[curtishavak]

I don't know if you will be able to do what you want with built-in Excel formulas, but I posted a UDF (user defined function) in this mini-blog article (Flexible Concatenation Function) that, as one of its abilities, will allow you to do what you asked.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConCat just like it was a built-in Excel function. For example, to produce the list you wanted...

=ConCat(CHAR(10),D:D)

Rick - this is a great solution to the problem that I was having. However, I have a question about modification of the delimiter. I am using your UDF and formula as follows:

=ConCat((CHAR(10) "-"),R2:AA2),

Where R2-AA2 is a matrix of order types placed by the contact name in each row. I want to reduce this list of rows to a single cell with a list of order types placed by that contact. For example:

From:
0: A/B/C/D/E
1: Name/Development Order/Standard Order/Sample Order/Literature
2: Peter/0/3/2/1
3: Jill/3/1/6/2

To:
0: A/B/C/D/E
1: Name/Order Types
2: Peter/-Standard Order
-Sample Order
-Literature
3: Jill/-Development Order
-Standard Order
-Sample Order
-Literature


Your formula works great, but rather than just the CHAR(10) delimiter, I would like to use CHAR(10) and a "-" to create a bulleted list and make it easier to see the separation when there is a long list of order types. How do I adjust the formula to create this combination?




I think I may have figured a solution out: ="-"&ConCat((CHAR(10)&"-"),R3:AA3) Let me know what you think or if there is a more elegant solution. Thanks.

 

[Rick Rothstein]

Your formula works great, but rather than just the CHAR(10) delimiter, I would like to use CHAR(10) and a "-" to create a bulleted list and make it easier to see the separation when there is a long list of order types. How do I adjust the formula to create this combination?

I think I may have figured a solution out: ="-"&ConCat((CHAR(10)&"-"),R3:AA3) Let me know what you think or if there is a more elegant solution. Thanks.

Yep, you figured it out. The delimiter argument is a String of one or more characters... if you need more characters and some of them are non-visible characters (or even characters stored in separate variables), you simply concatenate them together (as you figured out).