Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
Yes, there is a way to concatenate a series of cells using a user-defined function in Excel. Here's how you can do it:
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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. |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
concatenate with a varying range of cells | Excel Worksheet Functions | |||
concatenate cells by a given range | Excel Worksheet Functions | |||
function to concatenate range | Excel Discussion (Misc queries) | |||
concatenate a range function | Excel Worksheet Functions | |||
How to concatenate adjacent cells in a range without using &? | Excel Worksheet Functions |