Formula Too Long

C

carlos

Hi. I am using Excel 2002 Version 10.26.... and need to
enter the following formula but cannot fit it into 1 cell.
Is there a way to change this limitation ??

=master!I2&master!I3&master!I4&master!I5&master!I6&master!
I7&master!I8&master!I9&master!I10&master!I11&master!
I12&master!I13&master!I14&master!I15&master!I16&master!
I17&master!I18&master!I19&master!I20&master!I21&master!
I22&master!I23&master!I24&master!I25&master!I26&master!
I27&master!I28&master!I29&master!I30&master!I31&master!
I32&master!I33&master!I34&master!I35&master!I36&master!
I37&master!I38&master!I39&master!I40&master!I41&master!
I42&master!I43&master!I44&master!I45&master!I46&master!
I47&master!I48&master!I49&master!I50&master!I51&master!
I52&master!I53&master!I54&master!I55&master!I56&master!
I57&master!I58&master!I59&master!I60&master!I61&master!
I62&master!I63&master!I64&master!I65&master!I66&master!
I67&master!I68&master!I69&master!I70&master!I71&master!
I72master!I73&master!I74&master!I75&master!I76&master!
I77&master!I78&master!I79&master!I80&master!I81&master!
I82&master!I83&master!I84&master!I85&master!I86&master!
I87&master!I88&master!I89&master!I90&master!I91&master!
I92&master!I93&master!I94&master!I95&master!I96&master!
I97&master!I98&master!I99&master!I100&master!I101&master!
I102&master!I103&master!I104&master!I105&master!
I106&master!I107&master!I108&master!I109&master!
I110&master!I111&master!I112&master!I113&master!
I114&master!I115&master!I116&master!I117&master!
I118&master!I119&master!I120&master!I121&master!
I122&master!I123&master!I124&master!I125&master!I126
 
D

Daniel.M

Hi Carlos,

1. Rename your sheet to "m" :))

2. Or use a VBA function that concatenates a range of strings.

=MyConcat(master!I2:I126)

Function MyConCat(Rng As Range, _
Optional Delim As String = "") As String
Dim Cell As Range
For Each Cell In Rng
If Not Application.IsError(Cell) Then
MyConCat = MyConCat & Cell.Value & Delim
End If
Next Cell

End Function

Regards,

Daniel M.
 
J

Jason Morin

You could use user-defined function. I found this in the
google archives:

Public Function BigConcat(rng As Range) As String
Dim cell As Range
For Each cell In rng
BigConcat = BigConcat & cell.Text
Next cell
End Function

Then call with:

=bigconcat(master!I2:I126)

HTH
Jason
Atlanta, GA
 
D

Don Guillett

try a sub something like
sub stringem()
for each c in sheets("master").range("i2:i122")
mystr=mystr & c
next
'msgbox mystr
range("a4")=mystr
end sub
 
G

Guest

Carlos,
You could Concatenate your data in sections, and then concatenate the concatenations.

Good Luck,
Mark Graesser

----- carlos wrote: -----

Hi. I am using Excel 2002 Version 10.26.... and need to
enter the following formula but cannot fit it into 1 cell.
Is there a way to change this limitation ??

=master!I2&master!I3&master!I4&master!I5&master!I6&master!
I7&master!I8&master!I9&master!I10&master!I11&master!
I12&master!I13&master!I14&master!I15&master!I16&master!
I17&master!I18&master!I19&master!I20&master!I21&master!
I22&master!I23&master!I24&master!I25&master!I26&master!
I27&master!I28&master!I29&master!I30&master!I31&master!
I32&master!I33&master!I34&master!I35&master!I36&master!
I37&master!I38&master!I39&master!I40&master!I41&master!
I42&master!I43&master!I44&master!I45&master!I46&master!
I47&master!I48&master!I49&master!I50&master!I51&master!
I52&master!I53&master!I54&master!I55&master!I56&master!
I57&master!I58&master!I59&master!I60&master!I61&master!
I62&master!I63&master!I64&master!I65&master!I66&master!
I67&master!I68&master!I69&master!I70&master!I71&master!
I72master!I73&master!I74&master!I75&master!I76&master!
I77&master!I78&master!I79&master!I80&master!I81&master!
I82&master!I83&master!I84&master!I85&master!I86&master!
I87&master!I88&master!I89&master!I90&master!I91&master!
I92&master!I93&master!I94&master!I95&master!I96&master!
I97&master!I98&master!I99&master!I100&master!I101&master!
I102&master!I103&master!I104&master!I105&master!
I106&master!I107&master!I108&master!I109&master!
I110&master!I111&master!I112&master!I113&master!
I114&master!I115&master!I116&master!I117&master!
I118&master!I119&master!I120&master!I121&master!
I122&master!I123&master!I124&master!I125&master!I126
 
H

Harlan Grove

...
...
2. Or use a VBA function that concatenates a range of strings.

=MyConcat(master!I2:I126)

Function MyConCat(Rng As Range, _
Optional Delim As String = "") As String
Dim Cell As Range
For Each Cell In Rng
If Not Application.IsError(Cell) Then
MyConCat = MyConCat & Cell.Value & Delim
End If
Next Cell

End Function
Click to expand...
...

Leaves an instance of Delim dangling at the end of the result. My own variation,
which I've posted before, is


Function mcat(ParamArray s()) As String
'Copyright (C) 2002, Harlan Grove
'This is free software. It's use in derivative works is covered
'under the terms of the Free Software Foundation's GPL. See
'http://www.gnu.org/copyleft/gpl.html
'------------------------------------
'string concatenation analog to SUM
Dim r As Range, x As Variant, y As Variant

For Each x In s
If TypeOf x Is Range Then
For Each r In x.Cells
mcat = mcat & r.Value
Next r
ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & IIf(IsArray(y), mcat(y), y)
Next y
Else
mcat = mcat & x
End If
Next x
End Function


which works more like SUM. To add a delimiter between entries,

=SUBSTITUTE(mcat(Delim&Rng),Delim,"",1)

But better still, download and install Laurent Longre's MOREFUNC.XLL add-in from

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

It provides a function names MCONCAT, which works similar to how my mcat
function does but recalculates a lot faster than any VBA udf.
 
D

Daniel.M

Hi Harlan,
..

Leaves an instance of Delim dangling at the end of the result.
Click to expand...


Thanks for the heads up. Changes at the bottom of this msg.

My own variation,
which I've posted before, is

... coded function here ...

which works more like SUM. To add a delimiter between entries,

=SUBSTITUTE(mcat(Delim&Rng),Delim,"",1)
Click to expand...

Nice implementation of the Param in VBA.

But better still, download and install Laurent Longre's MOREFUNC.XLL add-in from

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

It provides a function names MCONCAT, which works similar to how my mcat
function does but recalculates a lot faster than any VBA udf.
Click to expand...

Agree with the speed.
But MCONCAT can't return strings of more than 256 bytes length (putting #VALUE).
A legacy of the 1 byte space reserved for strings length in XLOPER (and direct
call) structures (Excel <==> XLL).
So, in the case where you concatenate 125 cells as per the OP's request, they
better be non-verbose ;-)

Regards,

Daniel M.

Function MyConCat(Rng As Range, _
Optional Delim As String = "") As String
Dim Cell As Range
For Each Cell In Rng
If Not Application.IsError(Cell) Then
MyConCat = MyConCat & Cell.Value & Delim
End If
Next Cell
If Len(MyConCat) > Len(Delim) Then
MyConCat = Left(MyConCat, Len(MyConCat) - Len(Delim))
End If
End Function
 
H

Harlan Grove

...
...
But MCONCAT can't return strings of more than 256 bytes length (putting
#VALUE).
Click to expand...
...

Oops! You're correct.

...
If Len(MyConCat) > Len(Delim) Then
MyConCat = Left(MyConCat, Len(MyConCat) - Len(Delim))
End If
Click to expand...
...

The IF condition is always true unless the Rng argument refers to a single blank
cell or a single cell evaluating to "", so in most cases pointless, and in the
cases in which it's False a bug. If cell X99 were blank,

=MyConCat(X99,"###")

returns ###. What you need is

If Len(Delim) > 0 Then MyConCat = Left(MyConCat, Len(MyConCat) - Len(Delim))

But if you had noted the trick I used in the worksheet formulas in my previous
response, you'd have seen that it's more efficient to concatenate Delim before
each cell's value, then you could end with

If Len(Delim) > 0 Then MyConCat = Mid(MyConCat, Len(Delim) + 1)
 
D

Daniel.M

If Len(Delim) > 0 Then MyConCat = Left(MyConCat, Len(MyConCat) - Len(Delim))

But if you had noted the trick I used in the worksheet formulas in my previous
response, you'd have seen that it's more efficient to concatenate Delim before
each cell's value, then you could end with

If Len(Delim) > 0 Then MyConCat = Mid(MyConCat, Len(Delim) + 1)
Click to expand...

Got it finally. Thanks Harlan.

Daniel M.
 
Top