A simple UDF

Public Function MyJoin(ParamArray rng())
Dim i As Long
Dim cell As Range
Dim tmp As String

For i = LBound(rng) To UBound(rng)

For Each cell In rng(i)

If cell.Value < "" Then

tmp = tmp & "-" & cell.Value
End If
Next cell
Next i
If Len(tmp) 0 Then tmp = Right(tmp, Len(tmp) - 1)
MyJoin = tmp
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"FARAZ QURESHI" wrote in message
...
Hi every1

I have NINE columns showing different levels. An example:

Level 1 Level 2 Level 3 Level 4
A 1 a
A 2 a i
A 2 a ii
A 2 b i
B 1
B 2 a
B 2 b
C 1
C 2
D
E 1

Now what sort of SHORT formula should be placed in the column E (actually
10th column in the original data) which would concatinate the levels'
columns
resulting as follows, without using the IF() condition for placing "-"
(hyphens):

A - 1 - a
A - 2 - a - i
A - 2 - a - ii
A - 2 - b - i
B - 1
B - 2 - a
B - 2 - b
C - 1
C - 2
D
E - 1

--
Best Regards,
FARAZ A. QURESHI