Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shorter Concatenation (Excel97-SR2)

    Is there a shorter way of writing:

    =C3&D3&E3

    Where E3 could in fact extend to CC3?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Perhaps one of the Excel gurus will come up with a formula. I don't know how to do this without VBA.

    Here is a custom function that concatenates all cells in a range:

    Function ConcatenateRange(aRange As Range) As String
    Dim strReturn As String
    Dim aCell As Range
    For Each aCell In aRange.Cells
    strReturn = strReturn & aCell.Value
    Next aCell
    ConcatenateRange = strReturn
    End Function

    If the function is in a module in the current workbook, use it as follows:
    =ConcatenateRange(C3:S3)
    If you put the function in a module in another workbook, for instance Personal.xls:
    =Personal.xls!ConcatenateRange(C3:S3)
    Note: if the workbook name contains spaces, enclose it in single quotes:
    ='My Workbook'!ConcatenateRange(C3:S3)

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Thank you... I'd like to avoid VBA with this particular model. But I'll capture your idea for future use.

    Regards
    Peter

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Beware ON the SOAPBOX:
    One of my pet peeves of excel is no concatenate range:

    A1+A2+A3 is equiv to SUM(A1,A2,A3) but I don't think you gain anything moving to the function unless you shorten it to: Sum(A1:A3)
    BUT
    A1&A2&A3 is like concatenate(A1,A2,A3) and again no real gain (I find using the & quicker and easier than typing concatenate, which I usually misspell) but concatenate(A1:A3) does NOT work!

    Any Microsoft Excel Programmers listening??
    Steve

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Yes agreed, it is very odd some of the things you can't do easily. 3D arrays being another. Still, when they fix it, it will be in some future version. Right now, my employers are quite happy with Office 97, and I have to agree that I can see little real business benefit in upgrading. OK... Now to type some looooong concatenation commands...

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    For some reason, you wish not to use VBA to create the concatenated strings. That's fine. Have you considered modifying the VBA offered so you don't have to actually type your looooong concatenation commands? You could come up with a snippet of code to write the command for you, but that code does not have to be embedded in your spreadsheet - you could just stick it in Personal.xls until you no longer need it.

    That's just me - I can manage to justify spending 3.5 hours to write (that is, kludge my way through some) code that accomplishes what I probably could have manually finished in an hour.
    <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24> <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Hey now that IS smart idea! And I know exaxtly what you mean by 'clever bits of code'. Fraid I'm often guilty of the same. On this occassion however I was going to get smart with search and replace... Has to be away.....

    Regards
    Peter

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    If you don't want VB and you don't want to type long concatenation strings:
    Use the copy command to do it for you, all you waste some info in a row.
    Instead of +a1&A2& A3 & A4 &...& Ax
    In a blank col column (eg B1) enter +a1&B2 and copy it down the column. B1 will have concatenated(A1:Ax)

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Good idea, though won't help in this case... I'm on a mission to make my spreadsheet smaller in size. Trying to get rid of:

    =IF(DG6="","",DG6)&IF(D6<>E6,IF(DG6<>"",IF(RIGHT(D G6,1)=CHAR(10),"",", "),"")&IF(E6<>0,TEXT(E6,"0.0")&" from ","until ")&IF(E6<>0,TEXT(Planning!E$4,"ddd dd/mm/yy"),TEXT(Planning!E$4-3,"ddd dd/mm/yy")&"."&CHAR(10)),"")

    Which is in DG:HG by 40 rows.

    Basically what it does is concatenate itself with the previous cell. Since most of the cells it is testing will be 'empty' because they have the same value as their predecessor, I don't really need it to add anything. As it's currently constructed it will at minimum result in the same value as it predecessor, or extend it by adding to it. So what you end up with is the same string being copied 104 times, just so I can get the result at the end.

    I figure, it easier to change the above to only put something in the cell if the value being tested is different to the previous test value. That way most of the cells will contain nothing. Then all I need to do is concatenate them.. Oh lets not get started on that one again! ;-)

    Regards
    Peter

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    A cell with a formula that has a value of a null string is not "nothing", it is just as big as if the formula returned 255 chars of text! What is in the cell is the formula NOT the the value!

    IFs tend to be big memory hogs, it is better to keep the formula simple to save memory and calc time. Simple formulas (even in many cells tend) to be more efficient than even simple user defined functions in calc time.

    I would think 104 cells of a simple formula (even ones resulting in very large strings) would take up much less memory than 104 megaformulas that only gave null strings as the result!

    If you want to hide the display a custom format would work just as well.


    Also your formula could be reduced in size: the first part [IF(DG6="","",DG6)] does NOTHING. If DG6 is a null string, then use null string, otherwise (DG<>null) use DG6. Why not just use "DG6" it gives the same thing. Also you check that E6<>0 twice this:
    IF(E6<>0,TEXT(E6,"0.0")&" from "&TEXT(Planning!E$4,"ddd dd/mm/yy"), "until "&TEXT(Planning!E$4-3,"ddd dd/mm/yy")&"."&CHAR(10))
    should give the same thing and uses less IFs

    This is a litte smaller:
    =DG6&IF(D6<>E6,=IF(OR(DG3="",RIGHT(DG3,1)=CHAR(10) ),"",", ")&IF(E6<>0,TEXT(E6,"0.0")&" from "&TEXT(Planning!E$4,"ddd dd/mm/yy"), "until "&TEXT(Planning!E$4-3,"ddd dd/mm/yy")&"."&CHAR(10))
    ),"")

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Hi Steve

    Thanks! Amazing how easy you get into 'poor' coding habits!

    A question, and I'm showing my lack of Excel knowledge here, I understand, that the formula whatever it's results takes space and has to be stored somewhere. Surely though the result of that formula must also be stored somewhere? The string below is the result after 104 cells.

    3.0 from Mon 27/05/02, 5.0 from Mon 03/06/02, 2.0 from Mon 29/07/02, 5.0 from Mon 19/08/02, until Fri 04/10/02.

    That string however is complete by 4th Oct 2002, happens to be cell 28(ish) in the range, that means that the same result is copied/stored/calculated in the following 76 cells. Also, most of the intervening cells up to 28 result in a string being stored somewhere that isn't necessary?

    Tthis model is a resource estimating and tracking tool. The point of the calculation is to report to the user in a sort of English way when a resource is assigned or changes. There is a rolling two year calendar in use, hence the 104 cells. In many cases a resource signs on at the start and signs off at some point later. In that example there are only two entires required, though in worse case, the string could be completed by the second cell and copied throuh the following 102 cells. Don't know if I'm making any sense here now?

    As an aside.. All this stuff is in a hidden sheet. I only display the final result to a user in another sheet.

    Regards
    Peter

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    I am sorry to say I don't have the complete answer (I don't know enough about the "inner workings" of excel). I get much of my info from experience and some reading. Since my goal is USING excel, I don't always learn the details except by speculation on how it does things and what its "quirks" are.

    There are differences between calc efficiency and memory usage. You seem to have spent so much time/effort on the VALUE from the formula, than making the formula simple. I think a simple formula is better even if displays a lot of "long strings" this is only my opinion/speculation.

    I would give up some memory loss to make the spreadsheet easier to update and calc faster. I hate a sluggish spreadsheet. I think the loss in your case is small if you used the simple formula. This has the advantage of NOT explicitly typing in text strings (the formula is logic, coding, and text strings: I don't know how much is compiled and when)

    FORMULAS are more memory hogs than plain text, I have seen thru experience. So again I would think, better a simple formula (less memory than a complicated one and less calc issues). If you were going to ultimately "Paste-Special" you might have a point that returning Nulls is better than the intermediate concatenates, but you are not doing this, you are keeping the formulas so the results are "live".

    One thing I am sure of (thru some experimentations), better to use a megaformula, than a user-defined function, if you are worried about calc speed. UDFs are much slower (though they may be easier to debug and the logic could be made simpler). The difference might not be noticeable unless you use 1000s of copies of it, but it is real.

    I beleive that formulas are also more effiecient (memory and calc-wise) if you use cell references rather than absolutes.

    Instead of a cell being =B1*15 it is better to use =B1*$A$1 and have A1 = 15. In your megaformula, the custom format, the text, the values to compare, could all be in named ranges. It makes it easier to update all the equations and it is more "memory and calc friendly" (smaller and faster)

    I would be interested in the "academic question" of the memory issue with displays of a formula, but I think the number of cells (104) is not the large to worry about.

    I now step off my soapbox.

    Steve

  13. #13
    New Lounger
    Join Date
    Aug 2002
    Location
    Brussels, Belgium
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    Correct, but you can do it in 2 parts.
    I did it on a full row using MCONCAT(A1:AG1)&MCONCAT(CH1:IV1)
    Regards

  14. #14
    New Lounger
    Join Date
    Aug 2002
    Location
    Brussels, Belgium
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    <P ID="edit" class=small>(Edited by gwhitfield on 27-Oct-02 19:58. Hyperlinks added)</P>Sorry to come so late in the discussion.
    Some time ago I found an add-in (Morefun.dll or Morefunc.dll) at the following address http://longre.free.fr.
    This contains 35 functions.
    One of them MConcat resolve your problem. You only have to add the range to obtain result.

    Daniel

  15. #15
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shorter Concatenation (Excel97-SR2)

    <P ID="edit" class=small>(Edited by gwhitfield on 27-Oct-02 20:01. Hyperlinks added)</P>Steve,

    There is MCONCAT, part of the fast morefunc.xll add-in, downloadable from

    http://longre.free.fr/english/index.html

    However, I don't see much point in concatenating a huge number of cells.

    Aladin
    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •