You can go with the formulas that Peo and Jerry posted or with a UDF which makes
it a bit easier if you are comfortable with VBA.
And before you start note Peo's caveat about turning these into time values if
that's what you foresee.
Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ":"
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
=ConCatRange(A1:A3)
Or a macro which allows non-contiguous cells to be chosen.
Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub
Gord Dibben MS Excel MVP
