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)

On Sep 16, 9:35*am, "Rick Rothstein"
wrote:
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)

Good one. And super quick!

=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")))," ","/"),"-/","-")

Good one. And super quick!

Thanks, but you know I did not type all of that out, right? I ran a loop and
let it output the repetitive part and then just slapped the SUBSTITUTE parts
on the front and back it.

Rick