Function to concatenate cells in a range

[Leporello]

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.

 

[Jacob Skaria]

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

 

[Gord Dibben]

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

 

[stiell]

This is super helpful information - but I can't seem to get it to work for what I need and was hoping someone here might be able to give me a hand...

I'm trying to create a function that will help me pick a domain name - I have 4 columns of data (columns 1-3 = distinct sets of keywords, column 4 = domain suffixes (.com, .net, etc))

I would like to have a 5th column auto-populate with all the different variations that I can then chunk into godaddy as a text file.