• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code for UDF JoinText

P

pi_sa11

New Member
Hi,
I want vba code for UDF jointext where syntax of the function will =jointtext("A1:E1",",") which will give me o/p as below :

Suppose a b c d e are written in column A1 to E1 respectively then jointext function should return me answer as a,b,c,d,e.

Thanks in advance.
 

User defined function can (may / must / should) use existing functions !

Don't need to reinvent wheel with risk of a square one !
And built-in functions are fastest than any VBA code !
​
 
Hi ,

See if this helps :
Code: 
Public Function JoinText(inputrange As Range, Optional sep As String) As Variant
                Dim cell As Range
               
                For Each cell In inputrange
                    If cell.Text <> vbNullString Then
                      outstring = outstring & IIf(sep <> vbNullString, sep, ",") & cell.Text
                    End If
                Next
               
                JoinText = Mid(outstring, IIf(Len(sep) = 0, 1, Len(sep)) + 1)
End Function
Narayan
 
Another UDF way :​
Code: 
Function JoinRange$(Rg As Range, DELIM$)
    JoinRange = Join(Evaluate("TRANSPOSE(TRANSPOSE(" & Rg.Address(, , , True) & "))"), DELIM)
End Function
Do you like it ? So thanks to click on bottom right Like !


But it depends on the final purpose …
For example to create a csv text file, there are better ways
as you can see in some threads of this forum !
​
 
pi_sa11 said:
Hi,
I want vba code for UDF jointext where syntax of the function will =jointtext("A1:E1",",") which will give me o/p as below :

Suppose a b c d e are written in column A1 to E1 respectively then jointext function should return me answer as a,b,c,d,e.
Click to expand...
Different method.
Code: 
Function jointext(rng As Range, joinStr As String) As String
    jointext = Join(Filter(rng.Parent.Evaluate("if(" & rng.Address & _
    "<>""""," & rng.Address & ",cha(2))"), Chr(2), 0), joinStr)
End Function
 
OOps, should be Char(2)
Code: 
Function jointext(rng As Range, joinStr As String) As String
    jointext = Join(Filter(rng.Parent.Evaluate("if(" & rng.Address & _
    "<>""""," & rng.Address & ",char(2))"), Chr(2), 0), joinStr)
End Function
 
Because everyone knows you Jindon - you are a Jedi Master, your rep precedes you wherever you choose to post!!!!!:)
 
One more idea for one liner.
Code: 
Public Function JoinText(rng As Range, strDelim As String) As String
JoinText = Replace(Application.Trim(Join(Application.Transpose(Application.Transpose(rng.Value)), " ")), " ", strDelim)
End Function
 
You must log in or register to reply here.
Back
Top