I want to create an udf which will trim, concatenate proper the values

[ThrottleWorks]

I will be honest, do not know whether I am supposed to post this problem on the forum.

I have an assignment, I do not know how to resolve it.

I will be great full if someone assists me in this.


I have following data.

Cell a1, b1, c1, d1 data is text.


I want to create an udf which will trim, concatenate & proper the values in cell a1:d1.

The result will be populated in cell e1.

 

[Hui]

As a formula

=Proper(Trim(A1)&Trim(B1)&Trim(C1)&Trim(D1))

 

[ThrottleWorks]

Hui Sir, thanks a lot for the help.


If you get time could you please help me how to do this with UDF.


I am very poor with UDFs never used.


I know I am asking for too much, thanks once again for the formula.

 

[Debraj]

Hi Sachin,


Please try the below UDF..


In VBA > write the below Code..

[pre]

Function SBoyUDF(Source As Range, Optional delm As String = " ")
Dim cell As Range
For Each cell In Source
SBoyUDF = SBoyUDF & delm & StrConv(Trim(cell), 3)
Next cell
SBoyUDF = Mid(SBoyUDF, Len(delm) + 1)
End Function

[/pre]
Now in E1 as write formula as =SBoyUDF(A1:D1,",")


Regards,

Deb

 

[ThrottleWorks]

Hi Debraj, thanks a lot, have a nice weekend Sir.