Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to concatenate D1:D64?
What is the most succinct formula to concatenate D1:D64, which contain one
character each? I thought the following array formula would do the trick: =concatenate(index(D1:D64,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.) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to concatenate D1:D64?
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) -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to concatenate D1:D64?
The easiest way will be to install the MOREFUNC add-in.
http://www.download.com/Morefunc/300...-10423159.html Then you may try [among other helpful functions]: =MCONCAT(D1:D64,"") Micky "Joe User" wrote: What is the most succinct formula to concatenate D1:D64, which contain one character each? I thought the following array formula would do the trick: =concatenate(index(D1:D64,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.) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate #N/A | Excel Discussion (Misc queries) | |||
CONCATENATE | Excel Discussion (Misc queries) | |||
Concatenate | Excel Discussion (Misc queries) | |||
CONCATENATE | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |