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
|
|||
|
|||
Excel needs to expand text concatenation capability
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 |
#2
|
|||
|
|||
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 with Excel in the subject line. Cheers JulieD "Steve Schultz" Steve wrote in message ... 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 |
#3
|
|||
|
|||
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...m#vbatutorials for good places to start if you are not familiar with VBA. HTH. Best wishes Harald "Steve Schultz" Steve skrev i melding ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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...m#vbatutorials for good places to start if you are not familiar with VBA. HTH. Best wishes Harald "Steve Schultz" Steve skrev i melding ... 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 |
#5
|
|||
|
|||
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 "JulieD" skrev i melding ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Automatically Updating Excel Data In Word Doc Inside Excel File | Business Design Architect | General Discussion | 0 | August 29th, 2004 10:55 PM |
Add-in functions when starting Excel programmatically | Steve K. | Worksheet Functions | 11 | July 2nd, 2004 06:38 PM |
Coverting Lotus 123 files to use with excel | Muffin1947 | General Discussion | 6 | June 20th, 2004 10:18 AM |
Field code to pick up Excel sheet | Lyndie | Mailmerge | 1 | June 2nd, 2004 11:57 AM |
Cannot open large excel file | Niall Caslin | Worksheet Functions | 3 | October 29th, 2003 05:24 PM |