Array formula techniqes

J

Jack Schitt

Is it in theory possible to create a single-cell array formula that
simulates the effect of the following?



=Function(Mid(A1,1,1))&Function(Mid(A1,2,1)...&Function(Mid(A1,i,1)...&Funct
ion(A1,n,1)



where n is a variable equal to LEN(A1)

and "Function" is the same throughout

?



I have seen examples of array formulae that use

ROW(INDIRECT("1:"&LEN(A1)))

to identify the value of "i",

but the examples that I have seen always just SUM the elements instead of
concatenating them.



Is there a web page that talks me through these techniques, please? I had a
look at Chip Pearson's otherwise splendid site but the stuff on array
formulae does not seem to go into that depth (or perhaps I missed it).
 
H

Harlan Grove

Jack Schitt said:
I have seen examples of array formulae that use

ROW(INDIRECT("1:"&LEN(A1)))

to identify the value of "i",

but the examples that I have seen always just SUM the elements instead
of concatenating them.
Click to expand...

Generalized concatentation isn't possible in Excel using built-in functions
even involving array formulas. Excel simply doesn't provide anything more
than pairwise concatenation. So unlike + and SUM, for which {1,2}+{3;4} ==
{4,5;5,6} while SUM({1,2},{3;4}) == 10, & and CONCATENATE just do the same
thing, {1,2}&{3;4} == {"13","23";"14","24"} and CONCATENATE({1,2},{3;4})
returns the same array rather than "1234".

If you want general concatenation you have to use a udf.
Is there a web page that talks me through these techniques, please?
I had a look at Chip Pearson's otherwise splendid site but the stuff
on array formulae does not seem to go into that depth (or perhaps I
missed it).
Click to expand...

There's no theoretical treatise on array formulas. Learning by doing is
best, but next best is reading posts in this newsgroup and in
microsoft.public.excel.worksheet.functions. Search these newsgroups using
Google Groups on the term 'array'.
 
Top