Re: Excel needs to expand text concatenation capability

From: JulieD (JulieD_at_hctsReMoVeThIs.net.au)
Date: 03/20/05 

Date: Sun, 20 Mar 2005 23:57:59 +0800

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
---
Cheers
JulieD
"Harald Staff" <innocent@enron.invalid> wrote in message 
news:%23FyaEWTLFHA.3336@TK2MSFTNGP10.phx.gbl...
> 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
>
>
> "Steve Schultz" <Steve Schultz@discussions.microsoft.com> skrev i melding
> news:53A6020B-BFDA-47BF-9798-C21E1DC02D88@microsoft.com...
>> 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
>
>