Home |
Search |
Today's Posts |
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
html to excel | Excel Discussion (Misc queries) | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Merge from Excel to Excel | Excel Discussion (Misc queries) | |||
Shortcut file fails to open | Excel Discussion (Misc queries) | |||
pivotcell object to excel 2000 | Excel Discussion (Misc queries) |