Thread: Loooonng String Concatenation? (Excel 97-SR2)

1. Loooonng String Concatenation? (Excel 97-SR2)

Morning Folks

This mornings brain teaser.. I need to concatenate as text the numeric values in a 104 cells. I only wish however to report those cells which are different to their immediate predecessor. So, imagine something like:-

D E F G H I
5 0.5 0.5 0.5 1.0 1.0

(Note! Row 5, Column starts at D, D5 is blank in this example.. how do you post this layout here so it can be read?)

Would be reported as:

0.5 from Mon 08/04/02, 1.0 from Mon 29/04/02,

The dates are picked up from elsewhere.

Now, the formula for achieving that for just the first four cells looks like:

=IF(ISNUMBER(E5),IF(D5<>E5,TEXT(E5,"0.0")&" from "&TEXT(Planning!E\$3,"ddd dd/mm/yy")&", ",""),"")&IF(ISNUMBER(F5),IF(E5<>F5,TEXT(F5,"0.0") &" from "&TEXT(Planning!F\$3,"ddd dd/mm/yy"),""),"")&IF(ISNUMBER(G5),IF(F5<>G5,TEXT(G5,"0. 0")&" from "&TEXT(Planning!G\$3,"ddd dd/mm/yy")&", ",""),"")&IF(ISNUMBER(H5),IF(G5<>H5,TEXT(H5,"0.0") &" from "&TEXT(Planning!H\$3,"ddd dd/mm/yy")&", ",""),"")

I'm sure there must be a better way than having a 104 IF statements strung together? Assuming Excel would let me do that of course!

Regards
Peter

2. Re: Loooonng String Concatenation? (Excel 97-SR2)

Why not use a row and concatenate the cell to the left with the current cell, using your longish formula, copy this formula to the right and use the last cell.

Note that the string result of a formula will not display beyond about 1024 characters!

3. Re: Loooonng String Concatenation? (Excel 97-SR2)

Peter,

You wrote
<hr>how do you post this layout here so it can be read?<hr>
Jefferson Scher has posted a tutorial on <!post=How to make tables in the Lounge, 162644>How to make tables in the Lounge<!/post>. You can also download code for a macro that will copy an Excel (or Word) table to the clipboard ready for pasting into a post <!post=here, 164109>here<!/post>.

This table was created in Excel, copied to the clipboard using the macro and pasted into this post without further modification:

<table border=1><td></td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center valign=bottom>8</td><td valign=bottom>Sales</td><td valign=bottom>Jan</td><td valign=bottom>Feb</td><td valign=bottom>Mar</td><td align=center valign=bottom>9</td><td valign=bottom>North</td><td align=right valign=bottom>10</td><td align=right valign=bottom>12</td><td align=right valign=bottom>13</td><td align=center valign=bottom>10</td><td valign=bottom>West</td><td align=right valign=bottom>17</td><td align=right valign=bottom>15</td><td align=right valign=bottom>14</td><td align=center valign=bottom>11</td><td valign=bottom>South</td><td align=right valign=bottom>18</td><td align=right valign=bottom>19</td><td align=right valign=bottom>16</td><td align=center valign=bottom>12</td><td valign=bottom>East</td><td align=right valign=bottom>12</td><td align=right valign=bottom>11</td><td align=right valign=bottom>15</td></table>

4. Re: Loooonng String Concatenation? (Excel 97-SR2)

I was hoping to avoid duplicating rows, but yes, your solution may be best.

Given that I used my first array formulaonly this week, and I know think they are the coolest thing ever, so I probably over use them! I did wonder why some variant of the following. enteres as an array formula would't work:

{=IF(ISNUMBER(E5D5),IF(D5C5<>E5D5,TEXT(E5D5,"0.0")&" from "&TEXT(Planning!E\$3:Planning!DD\$3,"ddd dd/mm/yy")&", ",""),"")}

It doesn't, it returns only the first value.

Re the 1,024 byte text limit, I don't think it should be an issue here, resource allocation doesn't seem to fluctuate much across the planning period so I expect the text version of the resource plan to be relatively short.

Regards
Peter

5. Re: Loooonng String Concatenation? (Excel 97-SR2)

And for the thread wathchers out there... This is the formula I now have in the 104th cell:

=IF(HF5="","",HF5)&IF(ISNUMBER(DD5),IF(DC5<>DD5,IF (HF5<>"",IF(RIGHT(HF5,1)=CHAR(10),"",", "),"")&IF(DD5<>0,TEXT(DD5,"0.0")&" from ","until ")&IF(DD5<>0,TEXT(Planning!DD\$3,"ddd dd/mm/yy"),TEXT(Planning!DD\$3-3,"ddd dd/mm/yy")&"."&CHAR(10)),""),"")

Which in that case produces:

0.5 from Mon 08/04/02, 1.0 from Mon 29/04/02, until Fri 04/10/02.
3.0 from Mon 28/10/02, until Fri 08/11/02.
2.0 from Mon 25/11/02, until Fri 06/12/02.

Now, if someone wants to tidy that function up so that it resembles something *I* may stand a small chance of understanding what it does more than five minutes after I wrote it, you probably have too much time on your hands! ;-)

Regards
Peter

6. Re: Loooonng String Concatenation? (Excel 97-SR2)

Hi Hans

Thank you, I will investigate later.

Regards
Peter

7. Re: Loooonng String Concatenation? (Excel 97-SR2)

Peter,

The problem is your array formula:
{=IF(ISNUMBER(E5D5),IF(D5C5<>E5D5,TEXT(E5D5,"0.0")&" from "&TEXT(Planning!E\$3:Planning!DD\$3,"ddd dd/mm/yy")&", ",""),""))}

This gives you an Array of 104 "columns" but your formula does NOT do any concatenation with it. You can test it
{=index(IF(ISNUMBER(E5D5),IF(D5C5<>E5D5,TEXT(E5D5,"0.0")&" from "&TEXT(Planning!E\$3:Planning!DD\$3,"ddd dd/mm/yy")&", ",""),""),5)}

Should give you the 5th item.

Your array only displays the 1st one since that is all it can. It is like when you enter =RangeName in a cell. It only displays the upperleft value of RangeName, but index, average, or count(rangename), works on the entire thing.

If you selected the 104 columns, then F2, ctrl-shift-enter, it would create the ARRAY of all the values in the array for the display, but this still is no good to you, since you could get the same thing without the array formula, just enter the formula into each of the 104 cells!

What you need is a function to concatenate a range/Array!

Unfortunately, excel has no function to do this. You can use "&" or you can use: CONCATENATE (text1,text2,...), but there is nothing like CONCATENATE (E5D5) to give it an entire range NOT individual cells. (another of my excel pet peeves: (concatenate is just as difficult (if not more so ) than using "&" and takes the same amount of work. highlighting ranges would be a much better scheme)

Fortunately, posters can sometimes write their own functions.

Try this Array formula (I added the GroupMe function to your Array statement)

{=groupme(IF(ISNUMBER(E5D5),IF(D5C5<>E5D5,TEXT(E5D5,"0.0")&" from "&TEXT(Planning!E\$3:Planning!DD\$3,"ddd dd/mm/yy")&", ",""),""))}

You must add the function GroupMe into a VB module:

<pre>Function GroupMe(rng As Variant) As String
Dim myCell As Variant
GroupMe = ""
For Each myCell In rng
GroupMe = GroupMe & myCell
Next myCell

End Function
</pre>

Steve

8. Re: Loooonng String Concatenation? (Excel 97-SR2)

Hi Steve

Thanks, however I am specifically wishing to avoid VBA based solutions for this model. Hence the somewhat complicated solution I came up with!

Your solution is much neater since it avoids having to have a 40*105 cell array, just so I can calculate the 105th value for each of the 40 rows. Still, if I will restrict myself to pure workstream functions...

Regards
Peter

9. Re: Loooonng String Concatenation? (Excel 97-SR2)

You must "call it" with:

"PERSONAL.XLS!GroupMe("
not "GroupMe("

If it is in an addin, you do not need to state the filename.

These methods will allow the function without giving you "workbook contains macros" dialog (which I assume is what you really want to avoid)

Steve

10. Re: Loooonng String Concatenation? (Excel 97-SR2)

Hi Steve

The group I work with usually work remote from each other and also spend a lot of time e-mailing documents back and forth with external parties. For this reason I like to avoid any macros/VBA since it allows us to stick to our 'Never run macros' rule. It can be a pain but on balance for the best, certainly for our environment.

I promise, I'm not anti-VBA, I'd much rather have a simple '=ResourceSummary(D5D5,D\$3D\$5)' than the long winded solution I've elected for.

Regards
Peter

Posting Permissions

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