XL 2010 Concatenation données dans 1 seule cellule

Function Concat(ByVal TE) As String
   Dim X, TJN() As String, N As Integer
   If TypeOf TE Is Range Then TE = TE.Value
   For Each X In TE
      N = N + 1
      ReDim Preserve TJN(1 To N)
      TJN(N) = X
      Next X
   Concat = Join(TJN, " ")
   End Function

=Concat(C5:N5)

function ConcatAll(rng as range) as string
for i=1 to rng.count
    if rng(i)<>"" then ConcatAll=rng(i) &" " &ConcatAll
next i
end function

Function ConcatX(separateur As String, ParamArray t()) As String
Dim s, x, y
   For Each x In t
      If Not IsArray(x) Then
         s = Join(Array(s, x), separateur)
      ElseIf TypeName(x) = "Range" Then
         For Each y In x: s = Join(Array(s, y), separateur): Next
      End If
   Next x
   ConcatX = Mid(s, Len(separateur) + 1)
End Function