text join formula for Excel 2010

[rjplante]

I have a column of numbers and I would like them joined together into a single cell with two single quote mark at the front and each number surrounded by a single quote mark, and a column in between each number. The data below demonstrates my input and output.

Col A
BZX654456D
BZX123321D
BZX789987D

joined into Cell E1 as

' 'BZX654456D', 'BZX123321D', 'BZX789987D'


I am currently using the Textjoin function (see below) which works in Excel 2016, but not in Excel 2010. My end users are in Excel 2010 so I need to convert this formula into something that works for them. I do not care if it is a formula, a combination of formulas, or some elegant VBA code to make this happen. I currently have VBA in the spreadsheet so VBA might be the best way to get this done, however, I am open to suggestions.

My current formula: ="''" & TEXTJOIN("', '",TRUE,A1:A100) & "'"

Thanks for the help.

Robert

 

[shg]

UDF?

Function Cat(vInp As Variant, _
             Optional sSep As String = ",", _
             Optional bCatEmpty As Boolean = False) As String
  ' Catenates the elements of vInp separated by sSep
  ' Empty values and null strings are ignored unless bCatEmpty is True

  Dim vItem         As Variant
  Dim sItem         As String

  If bCatEmpty Then
    For Each vItem In vInp
      Cat = Cat & CStr(vItem) & sSep
    Next vItem

  Else
    For Each vItem In vInp
      sItem = CStr(vItem)
      If Len(sItem) Then Cat = Cat & sItem & sSep
    Next vItem
  End If

  If Len(Cat) Then Cat = Left(Cat, Len(Cat) - Len(sSep))
End Function

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]BZX654456D[/td][td="bgcolor:#E5E5E5"]''BZX654456D', BZX123321D', BZX789987D'[/td][td]B2: ="''" & IFERROR(textjoin("', '", TRUE, A1:A100), Cat(A1:A100, "', ")) & "'"[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]BZX123321D[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]BZX789987D[/td][td][/td][td][/td][/tr]
[/table]

That's in Excel 2010; 2016 users will get the result of the TEXTJOIN function.

 

[Aladin Akyurek]

Add the following code as a module to your workbook using Alt+F11...



Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Now control+shift+enter, not just enter:

="'"&REPLACE(aconcat(",'"&A1:A3&"'"),1,1,"")

 

[rjplante]

Thanks for the help to all. I will send this on to my end users and see if it works.

 

[jproffer]

Function Join(MyRange As Range, Begin As String, Div As String, Finish As String)
Dim rng As Variant
Dim temp As String




    For Each rng In MyRange
        temp = temp & rng & Div
    Next rng
            
    Join = Begin & temp & Finish
    
End Function

Excel 2013/2016

	A	B	C
BZX654456D	' 'BZX654456D', 'BZX123321D', 'BZX789987D', '' '
BZX123321D
BZX789987D

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=join(A1:A3,"' '","', '", "' '")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Maybe give that a try.

Syntax for the function is

=Join(Range, Beginning Text, Dividing Text, Ending Text)

= Join(A1:A3,"' '","', '", "' '")


EDIT: Too slow again .

 

[Rick Rothstein]

I don't have a version of Excel with the TEXTJOIN function in it, but based on online documentation, I believe the following UDF will perform the same. The argument list is identical to the real TEXTJOIN function although I don't think you will be able to specify as many items after the mandatory IgnoreBlanks argument as in the real function. Also, since I don't have access to the real function, I do not know if the order elements are read from a two-dimensional range or two-dimensional array is the same as the real function or not. With those provisos...

[table="width: 500"]
[tr]
	[td]Function TEXTJOIN(Delimiter As String, IgnoreBlanks As Boolean, ParamArray Text() As Variant) As String
  Dim Item As Variant, V As Variant, Arr As Variant
  For Each Item In Text
    If VarType(Item) > 8191 Then
      For Each V In Item
        If Len(V) > 0 Or (Len(V) = 0 And Not IgnoreBlanks) Then TEXTJOIN = TEXTJOIN & Delimiter & V
      Next
    Else
      TEXTJOIN = TEXTJOIN & Delimiter & Item
    End If
  Next
  TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)
End Function[/td]
[/tr]
[/table]

 

[asallame]

hello mr. shg
textjoin vba helped me put i use more than 2500 rows contains zero & blanks cells , how can i remove it ?

thank u