How to concatenate D1:D64?

[Joe User]

What is the most succinct formula to concatenate D164, which contain one
character each?

I thought the following array formula would do the trick:

=concatenate(index(D164,row(1:64)))

It didn't work. Well, I guess I understand why. CONCATENATE({"a","b"})
doesn't work either.

I finally resorted to a macro. But I'm still interested in an Excel
formula.

(Excel 2003 SP3.)

 

[OssieMac]

Hi Joe,

I don't think you will find a formula. However, did you create your code as
a User Defined Function (UDF)? Following is an example FYI and anyone else
who is looking for same solution.

Note: A space and underscore at the end of a line is a line break in an
otherwise single line of code.

Function ConCatRng(ByVal rngTarget As Range, _
Optional strSeparator As String)

Application.Volatile

Dim rng As Range

For Each rng In rngTarget
ConCatRng = ConCatRng & rng.Value _
& strSeparator
Next rng

'Remove the trailing separator (if any)
ConCatRng = Left(ConCatRng, _
Len(ConCatRng) - Len(strSeparator))

End Function

Separator is optional. Default is no separator.
Enter function like following for space separators.
=ConCatRng(A1:A25," ")

Enter function like following for no separator.
=ConCatRng(A1:A25)

 

[מיכ×ל (מיקי) ×בידן]

The easiest way will be to install the MOREFUNC add-in.
http://www.download.com/Morefunc/3000-2077_4-10423159.html
Then you may try [among other helpful functions]:
=MCONCAT(D164,"")
Micky