Formula too long

[apv98]

Hi,

Is there a different formula to replace

=A1 & REPT(" ",20-LEN(A1)) & B1 & REPT(" ",30-LEN(B1)) & C1 & REPT(
",4-LEN(C1)) & D1 & REPT(" ",4-LEN(D1)) & E1 & REPT(" ",8-LEN(E1)) & F
& REPT(" ",4-LEN(F1)) & G1 & REPT(" ",4-LEN(G1)) & H1 & REPT(
",7-LEN(H1)) & I1 & REPT(" ",40-LEN(I1)) & J1 & REPT(" ",3-LEN(J1))
K1 & REPT(" ",4-LEN(K1)) & L1 & REPT(" ",2-LEN(L1)) & M1 & REPT(
",3-LEN(M1)) & N1 & REPT(" ",2-LEN(N1)) & O1 & REPT(" ",7-LEN(O1)) & P
& REPT(" ",3-LEN(P1)) & Q1 & REPT(" ",2-LEN(Q1)) & R1 & REPT(
",7-LEN(R1)) & S1 & REPT(" ",3-LEN(S1)) & T1 & REPT(" ",10-LEN(T1))
U1 & REPT(" ",10-LEN(U1)) & V1 & REPT(" ",3-LEN(V1)) & W1 & REPT(
",14-LEN(W1)) & X1 & REPT(" ",5-LEN(X1)) & Y1 & REPT(" ",5-LEN(Y1))
Z11 & REPT(" ",10-LEN(Z1)) & AA1 & REPT(" ",10-LEN(AA1)) & AB1 & REPT(
",4-LEN(AB1)) & AC1 & REPT(" ",3-LEN(AC1)) & AD1 & REPT(" ",9-LEN(AD1)
& AE1 & REPT(" ",7-LEN(AE1)) & AF1 & REPT(" ",3-LEN(AF1)) & AG1
REPT(" ",7-LEN(AG1)) & AH1 & REPT(" ",3-LEN(AH1)) & AI1 & REPT(
",9-LEN(AI1)) & AJ1 & REPT(" ",3-LEN(AJ1))

I am running into a constraint as the formula is becoming too long.

Appreciate your help excel gurus.

Thanks & regards,
Pau

 

[Jim Rech]

One thing you can do is remove all the spaces before and after cell
references:

=A1 & REPT(" ",20-LEN(A1)) & B1

can become:

=A1&REPT(" ",20-LEN(A1))&B1

Other than that I'd suggest using 2 or more (out-of-view if necessary)
sub-formulas and then concatenating the results of them.

--
Jim Rech
Excel MVP
| Hi,
|
| Is there a different formula to replace
|
| =A1 & REPT(" ",20-LEN(A1)) & B1 & REPT(" ",30-LEN(B1)) & C1 & REPT("
| ",4-LEN(C1)) & D1 & REPT(" ",4-LEN(D1)) & E1 & REPT(" ",8-LEN(E1)) & F1
| & REPT(" ",4-LEN(F1)) & G1 & REPT(" ",4-LEN(G1)) & H1 & REPT("
| ",7-LEN(H1)) & I1 & REPT(" ",40-LEN(I1)) & J1 & REPT(" ",3-LEN(J1)) &
| K1 & REPT(" ",4-LEN(K1)) & L1 & REPT(" ",2-LEN(L1)) & M1 & REPT("
| ",3-LEN(M1)) & N1 & REPT(" ",2-LEN(N1)) & O1 & REPT(" ",7-LEN(O1)) & P1
| & REPT(" ",3-LEN(P1)) & Q1 & REPT(" ",2-LEN(Q1)) & R1 & REPT("
| ",7-LEN(R1)) & S1 & REPT(" ",3-LEN(S1)) & T1 & REPT(" ",10-LEN(T1)) &
| U1 & REPT(" ",10-LEN(U1)) & V1 & REPT(" ",3-LEN(V1)) & W1 & REPT("
| ",14-LEN(W1)) & X1 & REPT(" ",5-LEN(X1)) & Y1 & REPT(" ",5-LEN(Y1)) &
| Z11 & REPT(" ",10-LEN(Z1)) & AA1 & REPT(" ",10-LEN(AA1)) & AB1 & REPT("
| ",4-LEN(AB1)) & AC1 & REPT(" ",3-LEN(AC1)) & AD1 & REPT(" ",9-LEN(AD1))
| & AE1 & REPT(" ",7-LEN(AE1)) & AF1 & REPT(" ",3-LEN(AF1)) & AG1 &
| REPT(" ",7-LEN(AG1)) & AH1 & REPT(" ",3-LEN(AH1)) & AI1 & REPT("
| ",9-LEN(AI1)) & AJ1 & REPT(" ",3-LEN(AJ1))
|
| I am running into a constraint as the formula is becoming too long.
|
| Appreciate your help excel gurus.
|
| Thanks & regards,
| Paul
|
|
| ---
|
|

 

[AlfD]

i!

Short of a radical rethink, you might try a couple of palliatives.

1. You can assemble the pieces of the formula elsewhere.
e.g. =A1 & REPT(" ",20-LEN(A1)) could be assembled in a "helper" ro
out of the way (say A20000 to AJ20000) and the same for its friends
Then concatenate the pieces from the helper row a
concatenate(A20000,B20000,C20000,...,AJ20000).
Still pretty cumbersome.

2. You could write a UDF along these lines (I've only strung 5 bits ou
of the 36 you want, but there are only small changes involved)

Public Function concatlots()
Dim S As String
Dim Data As Variant
Data = Array(20, 30, 4, 4, 8)
With Worksheets("Sheet1")
For i = 1 To 5
S = S & Left((.Cells(1, i).Value & String(Data(i), " ")), Len(.Cells(1
i).Value) + Data(i))
Next i
End With
concatlots = S
End Function

Al