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]

Hello, and welcome to the board!

The only good way I can think to do this is with a VBA UDF, i.e.,

Public Function foo(ByRef rngIn As Range) As String
Dim tmpStr As String
Let tmpStr = Join(WorksheetFunction.Transpose( _
    rngIn), ", ")
Do While InStrB(1, tmpStr, ", ,", vbBinaryCompare)
    Let tmpStr = Replace$(tmpStr, ", ,", ",")
Loop
Let foo = tmpStr
End Function

Book1
	A	B	C	D
1	1	1, 4
2
3
4	4
Sheet1 (2)

There's actually a good reason, here, not to use .Value on the Range, if you're using xl 2000. :wink:

This has been given limited testing... Good hunting.

 

[barry houdini]

I assume that when you say "row A2 is deleted" you mean that the cell contents are deleted.

Four 4 cells as per your example you could use

=SUBSTITUTE(IF(A1="","",","&A1)&IF(A2="","",","&A2)&IF(A3="","",","&A3)&IF(A4="","",","&A4),",","",1)

but obviously this gets a bit cumbersome, if not completely unworkable, if you have a much bigger range.

In that situation I think you'd have to use code, as Nate suggests, or you could use MCONCAT function from MOREFUNC add-in and use

=SUBSTITUTE(MCONCAT(IF(A1:A4<>"",","&A1:A4,"")),",","",1)

confirmed with CTRL+SHIFT+ENTER

(untested)

 

[terrygtx]

Thank you both for the prompt reply.

I tried barry's method and it works great. Thank you again, very much.

As for your initial assumption, no. I meant the whole row (or column - which ever) is deleted - not just the cell contents. That was why I wanted an array formula. I wanted to specify the end points only to accomodate changes such as addtions and deletions and have the comma delimited list automatically refresh.

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?

t

 

[barry houdini]

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?
t

Yes, you need MOREFUNC installed or MCONCAT won't be recognised so Nate's proposal may suit you better

 

[terrygtx]

Yes, but will it error right off the bat? Or will it display the last calculated value from my machine and only error if a cell in the range is changed?

Sorry for asking such a basic question.

 

[barry houdini]

Terry,

I don't think that's a basic question, I didn't know the answer myself

I believe the last calculated value will be shown until the range is changed or recalculation takes place for any other reason

 

[Yogi Anand]

....
=SUBSTITUTE(MCONCAT(IF(A1:A4<>"",","&A1:A4,"")),",","",1)

confirmed with CTRL+SHIFT+ENTER

(untested)

Hi barry houdini:

Unless I am missing something here, using MCONCAT from the MoreFunc Add-in, wouldn't the following formula work ...

=MCONCAT(A1:A4,",")

the formula should work even if the cell contents of A2 get deleted or the entire row gets deleted.

What do you think?

 

[barry houdini]

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?

 

[Yogi Anand]

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?

Hi barry:

Can't say ... I am not uptodate with the latest stuff ... I am still using EXCEL 97!