Do you really want a formula? It would be easier to have the macro concatenate the values and put them in your selected cell like this...
  • First, select the cells you want to concatenate (Ctrl+Click each cell)
  • Run the Concat macro
  • The Macro prompts you to select the destination cell and places the concatenated result there.


Sub Concat()
    Dim cell As Range, strConcat As String, rng As Range
    For Each cell In Selection
        strConcat = strConcat & cell.Value & "; "
    Next
    strConcat = Left(strConcat, Len(strConcat) - 2)
    On Error Resume Next
    Set rng = Application.InputBox("Select the cell where you want the concatinated result.", "Concatinate Destination", Type:=8)
    On Error GoTo 0
    If Not rng Is Nothing Then rng.Value = strConcat
End Sub