Concatenate Text in an Array Formula

[terrygtx]

Hi all,

This is probably pretty easy, but I have searched with no luck.

What I would like to do is to concat a range of cells containing text values into a comma delimited list in a single cell.

i.e.

A1 = one
A2 = two
A3 = three
A4 = four

the resulting cell would be = one,two,three,four

Then if row A2 was deleted the result would be = one,three,four

TIA,

t

 

[NateO]

I had thought that MCONCAT would give multiple separators if there were multiple blanks, hence my unnecessarily convoluted construct.

Laurent must've built in a loop to check for multiple delimiters, as I did...

Although perhaps not, I haven't seen his source on this one... He might be using RegExp (which I would speculate is also looping, under-the-hood)...

My UDF won't work with XL '97; the Join() function wasn't added to the String Class until VB 6, i.e., it requires XL 2000+.

You'd want to be careful about how you recreate Join() in Excel '97; concatenating over and over again can prove to be very, very slow in VB[A].

However, if you have XL 2000+, the performance of my UDF should be pretty good; most of the functionality leverages compiled, machine-code.

 

[Aladin Akyurek]

...I suppose the drawback to this method over the one NateO proposed it that this will only work on a machine with the MOREFUNC add-in. Is that correct? In other words, if someone attempts to open this file on another machine (without morefunc), what will happen?...

The latest version of morefunc allows you to include/incorporate morefunc into the workbook you want to distribute. See the Tools|Morefunc option after installation.

 

[Aladin Akyurek]

Yogi,

Good call, that appears to be the case.

I had thought that MCONCAT would give multiple separators if there were multiple blanks, hence my unnecessarily convoluted construct.

has MCONCAT changed or is it just my ignorance?

Watch out with such qualifications as "my...construct"... If there are formula blanks in the range of interest or the range must be filtered by means of a conditional, wrapping MCONCAT inside a SUBSTITUTE is required. A range with just empty cells can be processed by an ordinary call to MCONCAT...

=MCONCAT(Range,",")

The whole issue has nothing to do with the version of Excel one is running.