Share via

Can I get a textjoin() add-in for 2013

Anonymous
2016-05-24T03:03:34+00:00

Can I get a textjoin() add-in  for 2013. I purchased my PC last fall and it is running excel 2013 and I really do not want to spend more money for just one application.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-05-24T05:22:31+00:00

Hi

You could create one if you don't mind a bit of dabbling with VBA. 

(Waring: I'm not familiar with textjoin() but wrote this code based on the description. Not extensively tested.)

Steps:

  1. Open a blank workbook
  2. Press ALT+F11 to open the VBA editor
  3. In the Project window on the left, right-click on the workbook name and choose Insert ->Module
  4. Paste this code in the resultant window

Option Explicit

Function TEXTJOIN(delimiter As String, ignore_empty As String, ParamArray textn() As Variant) As String

    Dim i As Long

    For i = LBound(textn) To UBound(textn) - 1

        If Len(textn(i)) = 0 Then

            If Not ignore_empty = True Then

                TEXTJOIN = TEXTJOIN & textn(i) & delimiter

            End If

        Else

            TEXTJOIN = TEXTJOIN & textn(i) & delimiter

        End If

    Next

    TEXTJOIN = TEXTJOIN & textn(UBound(textn))

End Function

  1. Save the workbook as an add-in type (.xlam)
  2. Load the add-in into Excel (File ->Options ->Addins)

Usage is the same as the real textjoin function except that text1 is also optional.

i.e. TEXTJOIN(delimiter, ignore_empty, text1, text2, ...., etc)

Regards

Murray

Was this answer helpful?

50+ people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-12-22T23:13:58+00:00

    Well this is a blast from the past. As noted previously I have no model to work off so the feedback is good.

    Try this code. It will accept a range, a single cell reference, a literal string value or an array of values.

    TEXTJOIN(delimiter, ignore_empty, text1, [text2], [....], etc)

    e.g. =TEXTJOIN(" ",FALSE,A1:A3,B1)

    =TEXTJOIN(" ",FALSE,"whippets",B2)

    =TEXTJOIN(" ",FALSE,{"I","love","whippets"},"a","lot")

    text1 is no longer optional.

    ignore_empty is boolean (TRUE/FALSE)

    Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray textn() As Variant) As String

        Dim i

        Dim rng

        For Each rng In textn

            If IsObject(rng) Or IsArray(rng) Then

                For Each i In rng

                    If Len(i) = 0 Then

                        If Not ignore_empty Then

                            TEXTJOIN = TEXTJOIN & i & delimiter

                        End If

                    Else

                        TEXTJOIN = TEXTJOIN & i & delimiter

                    End If

                Next

            Else

                If Len(rng) = 0 Then

                    If Not ignore_empty Then

                        TEXTJOIN = TEXTJOIN & rng & delimiter

                    End If

                Else

                    TEXTJOIN = TEXTJOIN & rng & delimiter

                End If

            End If

        Next

        TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - 1)

    End Function

    Regards

    Murray

    P.S. For future reference, since the post had been marked as answered, you're probably better off copy/pasting the thread into a new post to increase your chances of getting an answer. (e.g. "I found this brilliant code by some genius and want to know if it can be modified..."!!)

    Was this answer helpful?

    60+ people found this answer helpful.
    0 comments
  2. Anonymous
    2016-12-22T02:31:28+00:00

    This solution doesn't seem to allow ranges. I have to select each cell individually. Is there any way to modify it so that it accepts ranges?

    Otherwise fantastic solution!

    Was this answer helpful?

    5 people found this answer helpful.
    0 comments