Need Help With String Concatenation
Actually, this formula would be better as it hides the dash when the formula
is copied down through rows that do not have data filled in them yet...
=SUBSTITUTE(SUBSTITUTE(TRIM(B6&LEFT("-"&D$2,9*(D6="X"))&" "&LEFT(E$2,
9*E6="X"))&" "&LEFT(F$2,9*(F6="X"))&" "&LEFT(G$2,9*(G6="X"))&" "&LEFT(
H$2,9*(H6="X"))&" "&LEFT(I$2,9*(I6="X"))&" "&LEFT(J$2,9*(J6="X"))&" "&
LEFT(K$2,9*(K6="X"))&" "&LEFT(L$2,9*(L6="X"))&" "&LEFT(M$2,9*(M6="X"))&
" "&LEFT(N$2,9*(N6="X"))&" "&LEFT(O$2,9*(O6="X"))&" "&LEFT(P$2,9*(P6="X"))&
" "&LEFT(Q$2,9*(Q6="X"))&" "&LEFT(R$2,9*(R6="X"))&" "&LEFT(S$2,9*(S6="X"))&
" "&LEFT(T$2,9*(T6="X"))&" "&LEFT(U$2,9*(U6="X"))&" "&LEFT(V$2,9*(V6="X"))&
" "&LEFT(W$2,9*(W6="X"))&" "&LEFT(X$2,9*(X6="X"))&" "&LEFT(Y$2,9*(Y6="X"))&
" "&LEFT(Z$2,9*(Z6="X"))&" "&LEFT(AA$2,9*(AA6="X"))&" "&LEFT(AB$2,9*(
AB6="X"))&" "&LEFT(AC$2,9*(AC6="X")))," ","/"),"-/","-")
Rick Rothstein (MVP - Excel)
|