If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate lists
Is there an Easier way to Concatenate a number of cells greater than 50 cells
in once cell without using A1&"_"&A2&"_"& and so on. HAns |
#2
|
|||
|
|||
Concatenate lists
don't know how to populate 1 cell
but with your in A1:A50 in B1 insert = A1&"_" in B2 insert = B1&A2&"_" then drag/copy down On 21 Kwi, 14:52, Hanspeter wrote: Is there an Easier way to Concatenate a number of cells greater than 50 cells in once cell without using A1&"_"&A2&"_"& and so on. HAns |
#3
|
|||
|
|||
Concatenate lists
My suggestion would be to use an UDF. I think sooner or later, everyone
eventually realizes the need for something like this. This function will concatenate every cell within a selected range, and you can choose what your delimiter looks like. Install this into a Module in VBA. '========= Function ConcMe(r As Range, Optional x As String = ", ") As String For Each c In r 'If cell is blank, don't include If c.Value = "" Then GoTo NoInclude ConcMe = ConcMe & c.Value & x NoInclude: Next c 'Remove final delimiter ConcMe = Left(ConcMe, Len(ConcMe) - Len(x)) End Function '========== Then, back in your workbook, the formula is: =ConcMe(A1:A50,"_") -- Best Regards, Luke M "Hanspeter" wrote in message ... Is there an Easier way to Concatenate a number of cells greater than 50 cells in once cell without using A1&"_"&A2&"_"& and so on. HAns |
#4
|
|||
|
|||
Concatenate lists
You will have to use a 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) "Hanspeter" wrote: Is there an Easier way to Concatenate a number of cells greater than 50 cells in once cell without using A1&"_"&A2&"_"& and so on. HAns |
Thread Tools | |
Display Modes | |
|
|