Prev: Function help please.
Next: Simple hack to get $500 to your home.
From: Leporello on 3 Jun 2010 11:24
Does anyone have an elegant way of concatenating a series of cells? I think
the answer will be a user defined function which will be equivalent to
MULTICONCAT(start cell : end cell). I think it needs to be a function rather
than a macro, so that I can include it in formulae elsewhere in the workbook.
I can manage with the limitation of the contents of the cells of a single
row or a single column, but it would be nice to have something which was a
bit more versatile and could handle a two dimensional array or, best of all,
non-contiguous cells.
From: Jacob Skaria on 3 Jun 2010 11:38
Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Syntax:
=CONCATRANGE(rngRange,strDelimiter,blnIgnoreBlank)
rngRange is the Range
strDelimiter Optional . Default is space
blnIgnoreBlank Optional. Default is False

Examples:
'1. Concatenate with default delimiter(space)
=CONCATRANGE(A1:A10)

'2. Concatenate with semicolon as delimiter and ignore blanks
=CONCATRANGE(A1:A10,":",1)

Function CONCATRANGE(rngRange As Range, _
Optional strDelimiter As String = " ", _
Optional blnIgnoreBlank As Boolean = False)
Dim varTemp As Range
For Each varTemp In rngRange
If blnIgnoreBlank Then
If Trim(varTemp) <> vbNullString Then _
CONCATRANGE = CONCATRANGE & strDelimiter & varTemp
Else
CONCATRANGE = CONCATRANGE & strDelimiter & varTemp
End If
Next
CONCATRANGE = WorksheetFunction.Trim(Mid(CONCATRANGE, _
len(strDelimiter)+1))
End Function

--
Jacob (MVP - Excel)


"Leporello" wrote:

> Does anyone have an elegant way of concatenating a series of cells? I think
> the answer will be a user defined function which will be equivalent to
> MULTICONCAT(start cell : end cell). I think it needs to be a function rather
> than a macro, so that I can include it in formulae elsewhere in the workbook.
> I can manage with the limitation of the contents of the cells of a single
> row or a single column, but it would be nice to have something which was a
> bit more versatile and could handle a two dimensional array or, best of all,
> non-contiguous cells.
From: Gord Dibben on 3 Jun 2010 18:54
Function ConCatRange22(CellBlock As Range, Optional Delim As String = "") _
As String

'entered as =concatrange22(a1:a10,"|") desired delimiter between quotes
'or as =concatrange22((a1:a10,e1,f1,g1:g4)"|") for non-contiguous ranges.

Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text <> "" Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange22 = Left(sbuf, Len(sbuf) - Len(Delim))

End Function


Gord Dibben MS Excel MVP


On Thu, 3 Jun 2010 08:24:10 -0700, Leporello
<Leporello(a)discussions.microsoft.com> wrote:

>Does anyone have an elegant way of concatenating a series of cells? I think
>the answer will be a user defined function which will be equivalent to
>MULTICONCAT(start cell : end cell). I think it needs to be a function rather
>than a macro, so that I can include it in formulae elsewhere in the workbook.
> I can manage with the limitation of the contents of the cells of a single
>row or a single column, but it would be nice to have something which was a
>bit more versatile and could handle a two dimensional array or, best of all,
>non-contiguous cells.

 | 
Pages: 1
Prev: Function help please.
Next: Simple hack to get $500 to your home.