http://www.excelforum.com/excel-prog...ate-macro.html


Changes:
Macro breaks if range (rngA) has only 1 line of data
Change concatenate to populate in ,9 instead of ,6
Concatenate eliminate * and - (astricks and dashes)
Change =LEN formula to populate in ,10 instead of ,8
Have number from rngA populate in ,8 without the dash


Sub Concat_Long_Text()
    Dim rngA As Range
    For Each rngA In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
        If Application.CountA(rngA.Offset(, 6)) > 0 Then
            rngA(rngA.Count + 1, 6).Value = Join(Application.Transpose(rngA.Offset(, 6)))
            rngA(rngA.Count + 1, 8).FormulaR1C1 = "=LEN(RC[-2])"
        End If
    Next
End Sub