Need Help in VBA UDF Function

[Prasad K]

here i have got a udf function of concat function and it's working perfectly in given range to combine text string & here what the problem is if the text string have extra space in cell range
and this udf function never trimming the extra space to single space

i have applied this udf function in excel 2016

Function Concat(Rng As Range) As String
Dim cl As Range
Concat = ""
For Each cl In Rng
Concat = Concat & cl.Value & " "
Next cl
Concat = Trim(Concat)
End Function

 

[StephenCrump]

Concat = Concat & Trim(cl.Value) & " "

But I think you have Excel's CONCAT() function in Excel 2016, so you should change the name of your UDF.

 

[Prasad K]

Concat = Concat & Trim(cl.Value) & " "

No Sir i don't have concat function in my excel 2016 that's why i applied this udf function

i have tried this line of code in my udf function

i getting #Value error

Function Concat(Rng As Range) As String
Dim cl As Range
Concat = ""
For Each cl In Rng
Concat = Concat & cl.Value & " "
Next cl
Concat = Concat & Trim(cl.Value) & " "
End Function

 

[StephenCrump]

Like this:

Function Concat(Rng As Range) As String
   
    Dim cl As Range
   
    Concat = ""
    For Each cl In Rng
        Concat = Concat & Trim(cl.Value) & " "
    Next cl
    Concat = Trim(Concat)

End Function

 

[Prasad K]

Like this:

Function Concat(Rng As Range) As String
  
    Dim cl As Range
  
    Concat = ""
    For Each cl In Rng
        Concat = Concat & Trim(cl.Value) & " "
    Next cl
    Concat = Trim(Concat)

End Function

Thank you so much
it's worked perfect

 

[StephenCrump]

Great, I'm glad we could help.