Flexible Concatenation Function

[Rick Rothstein]

Rick, data can go into the adjacent columns to the right of the data fields. All is text, but that field shows an error because the cell starts with an "=".

I'm glad I asked about that #NAME? error as those lines of code required special handling. Give this macro a try...

Sub ConcatDashedRanges()
  Dim NextRow As Long, TempText As String, EmptyCells As Range, EqualSignCells As Range, Ar As Range
  On Error Resume Next
  Set EqualSignCells = Columns("A").SpecialCells(xlFormulas)
  For Each Ar In EqualSignCells
    Ar.Value = "'" & Ar.Formula
  Next
  Set EmptyCells = Columns("A").SpecialCells(xlConstants)
  For Each Ar In EmptyCells.Areas
    NextRow = NextRow + 1
    TempText = Application.Trim(Join(Application.Transpose( _
               Ar.Offset(1).Resize(Ar.Count - 1).Value), ";"))
    If Left(TempText, 1) = "=" Then TempText = "'" & TempText
    Cells(NextRow, "B").Value = TempText
  Next
End Sub

I highlighted in red the location where the delimiter is specified in case you want to change it in the future.