VBA to Concatenate Multi-Value Cells

[Kenneth Hobs]

Note that in my function, you need to set the reference by clicking Tools menu, References, and add that one.

Sub semicolonTobackslash()  
  Dim r As Range
  Dim s() As String, ss() As String, str As String
  Dim i As Integer, j As Integer, k As Integer
  
  Set r = Range("A2").Resize(1, 4)
  On Error Resume Next
  
  Do Until r(1, 1).Value = ""
    r.Copy
    s() = Split(getClipboard, vbTab)
    
    j = UBound(Split(s(0), ";"))
    str = ""
    
    For k = 0 To j
      For i = 0 To UBound(s)
        ss() = Split(s(i), ";")
        If Left(str, 1) <> "" Then
          str = str & "\" & Trim(ss(k))
          Else
          str = ss(k)
        End If
      Next i
      str = str & "; "
    Next k
    
    str = Replace(Replace(str, vbCrLf, ""), "; \", "; ")
    str = Left(str, Len(str) - 2)
    Range("E" & r.Row).Value = str
    
    Set r = r.Offset(1)
  Loop
  
  Application.CutCopyMode = False
End Sub


Function getClipboard()
'Add Reference:   'Reference: Microsoft Forms 2.0 Object
    Dim MyData As DataObject
     
    On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function