ExcelBanter - Creating a long string of text...

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Creating a long string of text... (https://www.excelbanter.com/excel-worksheet-functions/134907-creating-long-string-text.html)

Creating a long string of text...

Hi there,

I'm trying to figure out a better way to do this - since the formula is
already too long for the cell (and I don't want to split it between two
cells, I want to find a better, more automatic way.

So this is my formula (same thing repeated with more cells)

=IF(BK$6="DE",($B$6 & ", "),"")&(IF(BK$7="DE",$B$7 & ",
",""))&(IF(BK$8="DE",$B$8 & ", ",""))&(IF(BK$9="DE",$B$9 & ",
",""))&(IF(BK$10="DE",$B$10 & ", ",""))....etc.

I've looked at Lookup, but it doesn't seem to allow for finding numerous
occurances and listing them out. I could be mistaken though.

Please help!

Thank you,
Alison

Creating a long string of text...

AlieSlavin wrote...
I'm trying to figure out a better way to do this - since the
formula is already too long for the cell (and I don't want to
split it between two cells, I want to find a better, more
automatic way.

So this is my formula (same thing repeated with more cells)

=IF(BK$6="DE",($B$6 & ", "),"")
&(IF(BK$7="DE",$B$7 & ", ",""))
&(IF(BK$8="DE",$B$8 & ", ",""))
&(IF(BK$9="DE",$B$9 & ", ",""))
&(IF(BK$10="DE",$B$10 & ", ",""))....etc.

I've looked at Lookup, but it doesn't seem to allow for finding
numerous occurances and listing them out. I could be mistaken
though.

Excel doesn't provide a built-in function for generalized
concatenation. You could try Laurent Longre's MOREFUNC.XLL add-in,
from http://xcell05.free.fr/english/, which provides an add-in
function named MCONCAT, which you could use in an array formula like

=MCONCAT(IF($BK$6:$BK$1000="DE",$B$6:$B$1000&", ",""))

The problem with MCONCAT is that it's subject to the XLL interface's
inability to return strings longer than 255 characters. If your
concatenated string could exceed 255 characters, you'd need to use
VBA. One possibility may be found in the following article from the
Google Groups archive.

http://groups.google.com/group/micro...456a9e326b19a6

(or http://tinyurl.com/2go6pf ).

Creating a long string of text...

AlieSlavin wrote...
That returned just the first match, which is great, but I need all
of the matches.

It's so annoying because I know how to do this in VBA script by
looping it, but I want this to be via Worksheet Function.
....

Then if you have, say, 1000 cells you need to check in col BK, so you
might need to concatenate up to 1000 cells in col B, the easiest
approach would be to use 1000 other cells to generate the result.

IV6:
=IF($BK$6="DE",$B$6&", ","")

IV7:
=IV6&IF($BK$7="DE",$B$7&", ","")

Fill IV7 down into IV8:IV1005. Cell IV1005 would evaluate to what you
want. If you want to use fewer cells, put more of the IF calls into
each cell. Excel doesn't make this easy to do with formulas.

Creating a long string of text...

MCONCAT was great, but it wouldn't work the if. So I made a silly, but
functional work around using it.

I created another sheet to match my 1st one. Only in the cells that would
have contained "DE" I put in
=IF(Sheet1!BJ5="DE",($B5 &", "),"")

Then on the main sheet used
=MCONCAT(DE!BJ5:BJ37)

Worked like a charm. :-)

Thank you to everyone for your help!!

"Harlan Grove" wrote:

AlieSlavin wrote...
I'm trying to figure out a better way to do this - since the
formula is already too long for the cell (and I don't want to
split it between two cells, I want to find a better, more
automatic way.

So this is my formula (same thing repeated with more cells)

=IF(BK$6="DE",($B$6 & ", "),"")
&(IF(BK$7="DE",$B$7 & ", ",""))
&(IF(BK$8="DE",$B$8 & ", ",""))
&(IF(BK$9="DE",$B$9 & ", ",""))
&(IF(BK$10="DE",$B$10 & ", ",""))....etc.

I've looked at Lookup, but it doesn't seem to allow for finding
numerous occurances and listing them out. I could be mistaken
though.

Excel doesn't provide a built-in function for generalized
concatenation. You could try Laurent Longre's MOREFUNC.XLL add-in,
from http://xcell05.free.fr/english/, which provides an add-in
function named MCONCAT, which you could use in an array formula like

=MCONCAT(IF($BK$6:$BK$1000="DE",$B$6:$B$1000&", ",""))

The problem with MCONCAT is that it's subject to the XLL interface's
inability to return strings longer than 255 characters. If your
concatenated string could exceed 255 characters, you'd need to use
VBA. One possibility may be found in the following article from the
Google Groups archive.

http://groups.google.com/group/micro...456a9e326b19a6

(or http://tinyurl.com/2go6pf ).