Excel needs to expand text concatenation capability

[Steve Schultz]

I would love to be able to concatenate any non-blank entries from a list.
Unfortunately, I can't seem to do that other than manually entering each cell
reference into a CONCATENATE() command. What I'd love to see, in order of
preference, is:

CONCATENATE(A1:A100) - Pretty obvious
CONCATENATEIF(Range,Criteria,Concat_Range) - Like SUMIF, but concatenates
DCONCATENATE(Database,Field,Criteria) - Like DSUM, but concatenates

 

[JulieD]

Hi Steve

this is just a peer-to-peer user support forum ... so if you'ld like to make
your wishes known to MS email them at
(e-mail address removed)

with Excel in the subject line.

Cheers
JulieD

 

[Harald Staff]

Hi

Those are very good ideas. Ok, today you'll learn how to fix those things
yourself instead of waiting for features like that in future Excel versions.
Hooray !

Open the workbook in question. Open the VB editor (Tools > Macros menu, or
Alt F11, or similar).

There choose menu Insert > Module. You will get what looks like a blank text
document. That is a module, a placeholder for VB code. Write, or paste, this
exact text into the module:

' **** start ***
Function CONCAT_RANGE(ConcRange As Range, _
Optional DelimitWith As String) As String
Dim Cel As Range
For Each Cel In ConcRange
If Cel.Text <> "" Then CONCAT_RANGE = _
CONCAT_RANGE & Cel.Text & DelimitWith
Next
If CONCAT_RANGE <> "" Then _
CONCAT_RANGE = Left$(CONCAT_RANGE, _
Len(CONCAT_RANGE) - Len(DelimitWith))
End Function
' **** end ***

Now return to Excel. With something somewhere in rangeA1:A100, select any
cell i B column. Go menu Insert > Function. Choose category User defined.
Choose CONCAT_RANGE. Fill the ConcRange field with A1:A100. Leave
DelimitWith blank if you want, or enter a comma, a space or whatever if you
want that something to separate the items. OK, and you have it.

The code you put in the module ia VBA code, Visual Basic for Applications.
It's an english-looking programming language used for macros and for custom
functions like that. See
http://www.mvps.org/dmcritchie/excel/excel.htm#vbatutorials
for good places to start if you are not familiar with VBA.

HTH. Best wishes Harald

 

[JulieD]

liked Harald's Concat_Range so much thought i would try my hand at the
Concat_IF one - this is my first version which really needs some work in
making the range_to_concatenate flexible - this code will concatenate the
column to the left of the range_to_check (ConcRange)

---
Public Function CONCAT_IF(ConcRange As Range, ConcCrit As String, _
Optional DelimitWith As String) As String

Dim Cel As Range
For Each Cel In ConcRange

If Cel.Text = ConcCrit Then CONCAT_IF = _
CONCAT_IF & Cel.Offset(0, 1).Text & DelimitWith
Next
If CONCAT_IF <> "" Then _
CONCAT_IF = Left$(CONCAT_IF, _
Len(CONCAT_IF) - Len(DelimitWith))
End Function

 

[Harald Staff]

Hi Julie

Nice.
This is good for spesific tasks. If we were to write these as general
solutions, the Criteria parameter should be able to handle inputs like

12
Julie
=C14
=C14/B14
=12*C14
=MAX(12,C14)

Should be a fun thing to do. Hope I'll find time to play with it.

Best wishes Harald