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

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

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

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