Excel VBA - String Conactenation with delimiter and single quotes

Question

I have a column of cells. They need to be concatenated into a single string with multiple delimiters in between.

I need something like the result in column 2 from the values in column 1

Column 1 | column 2
a1       |   'a1'
a2       |    'a1';'a2'
a3       |    'a1';'a2';'a3'
a4       |    'a1';'a2';'a3';'a4'
a5       |    'a1';'a2';'a3';'a4';'a5'

Currently I use the following formula

Column 1  |   Column 2
a1        |  ="'"&a1&"'"&";"
a2        |  =b1&"'"&a2&"'"&";"
a3        |

and copy it down the column B2.

Is there a VBA code that could help me do that. I did find some that would add a single delimiter ' between the cells but I could not modify it to add multiple delimiters.

It would be very helpful could share a VBA code for the same.

If you are using Excel 2016, the `TEXTJOIN` function is all you would need — CallumDA, Mar 24 '17 at 17:29
Possible duplicate of [How to add single quote and comma to all values in a column?](http://stackoverflow.com/questions/42915255/how-to-add-single-quote-and-comma-to-all-values-in-a-column) — CallumDA, Mar 24 '17 at 17:30
Just asked and answered three times at [Excel VBA UDF for concatenating is giving an Error message](http://stackoverflow.com/questions/43004691/excel-vba-udf-for-concatenating-is-giving-an-error-message/43005063#43005063). — , Mar 24 '17 at 18:33
Thanks everyone for all your help. I will try all these methods and let you know which worked the best for me. Thanks again for everyone for all your help. — Shrilakshmi Bartur, Mar 27 '17 at 17:05

score 4 · Answer 1 · answered Mar 24 '17 at 17:28

4

You can use the Join method in VBA with a given delimeter:

e.g.

someArray = Array("some", "words", "here")
Debug.Print "'" & Join(someArray, "';'") & "'"

'// will print:
'// 'some';'words';'here'

answered Mar 24 '17 at 17:28

SierraOscar

17,507
6
40
68

score 3 · Answer 2 · answered Mar 24 '17 at 18:12

I know you asked for VBA, but you can do this with a formula:

In B2:

=IF(ROW()=2,TEXTJOIN(";",TRUE,"'"&B1,"'"&A2&"'"),TEXTJOIN(";",TRUE,B1,"'"&A2&"'"))

and drag down:

Or

In B1, use ="'"&A1&"'", then in B2 (and drag down):

=SUBSTITUTE(TEXTJOIN(";",TRUE,"'"&B1,"'"&A2&"'"),"'","",1)

ManishChristian · Answer 3 · 2017-03-24T18:55:49.220

2

You can achieve desired output using this formula:

=CONCATENATE(B2, " ; '", A3, "'")

First put value from cell A2 to B2 manually (using ="'" & A2 & "'" formula) and then paste this formula in cell B3 and drag it down.

Here is the result with updated formula:

UPDATE (Thanks to BruceWayne)

Enter the value in cell B2 using:

="'" & A2 & "'"

So it will take the first '

edited Mar 24 '17 at 18:55

answered Mar 24 '17 at 17:35

ManishChristian

3,759
3
22
50

That seems to just repeat the `A` column value, instead of using the current row, plus the previous rows. – BruceWayne Mar 24 '17 at 17:50
Ah, I misunderstood the question. Thanks for pointing it out. – ManishChristian Mar 24 '17 at 17:54
Ah nice, this a bit is better than mine :D . Just to note, I'd change `B2` to `="'"&A1&"'"` to get the very first `'` leading `a1'`. – BruceWayne Mar 24 '17 at 18:22
1

Ah good catch again. I don't somehow I missed it. I think I need coffee now. :D – ManishChristian Mar 24 '17 at 18:49

score 1 · Answer 4 · answered Mar 24 '17 at 17:55

1

I would just do a simple loop like so.

Sub combineRows()

    'start and end rows, assuming column A
    Dim startRow, endRow As Integer

    Dim myString, myAdd As String

    startRow = 2
    endRow = 6



    For i = startRow To endRow


        myAdd = "'" & Range("A" & i) & "'" & ";"

        myString = myString + myAdd

        Range("B" & i) = myString

    Next i



End Sub

answered Mar 24 '17 at 17:55

Robomato

257
1
2
13

Hi, Thanks for this simple code. It is pretty easy. However I am having two problems. 1. The first ' is missing for the first entry in all rows 2. A ";" is remaining after the last entry in all rows Can you please help me with this is issue? Why is it happening? Can it be fixed? Thanks – Shrilakshmi Bartur Apr 02 '17 at 17:36

Dick Kusleika · Answer 5 · 2017-04-03T14:50:32.747

Here's my JoinRange function. It has a few more options than you're looking for.

Public Function JoinRange(rInput As Range, _
     Optional sDelim As String = vbNullString, _
     Optional sLineStart As String = vbNullString, _
     Optional sLineEnd As String = vbNullString, _
     Optional sBlank As String = vbNullString, _
     Optional sQuotes As String = vbNullString, _
     Optional IgnoreBlanks As Boolean = True) As String

     Dim vaCells As Variant
     Dim i As Long, j As Long
     Dim lCnt As Long
     Dim aReturn() As String

     If rInput.Cells.Count = 1 Then
        ReDim aReturn(1 To 1)
        aReturn(1) = sQuotes & rInput.Value & sQuotes
     Else
        vaCells = rInput.Value
         ReDim aReturn(1 To rInput.Cells.Count)

         For i = LBound(vaCells, 1) To UBound(vaCells, 1)
             For j = LBound(vaCells, 2) To UBound(vaCells, 2)
                 If Len(vaCells(i, j)) = 0 Then
                     If Not IgnoreBlanks Then
                         lCnt = lCnt + 1
                         aReturn(lCnt) = sQuotes & sBlank & sQuotes
                     End If
                 Else
                     lCnt = lCnt + 1
                     aReturn(lCnt) = sQuotes & vaCells(i, j) & sQuotes
                 End If
             Next j
         Next i

         ReDim Preserve aReturn(1 To lCnt)
     End If

     JoinRange = sLineStart & Join(aReturn, sDelim) & sLineEnd

End Function

use it in B1 like

=JoinRange($A$1:A1,";")

and fill down.

Hi, I tried the code but it is not adding the leading and ending single quote (') Can you please point out what I am missing? — Shrilakshmi Bartur, Apr 02 '17 at 17:25
Sorry, I missed the quotes. Use it like `=JoinRange($A$1:A1,";",,,,"'")`. That last argument is double quote - single quote - double quote. — Dick Kusleika, Apr 03 '17 at 14:48
Note also that I updated the code to fix a bug where a single cell range didn't get handled properly. — Dick Kusleika, Apr 03 '17 at 14:51

score 0 · Answer 6 · edited Jun 20 '20 at 09:12

_{For the sake of avoiding visual confusion, I will offer CHAR(39)&CHAR(59)&CHAR(39) in place of "';'".}

In B1, use this:

=CHAR(39)&TEXTJOIN(CHAR(39)&CHAR(59)&CHAR(39), TRUE, A$1:A1)&CHAR(39)

Fill down.

... or if the end result is the only important thing,

=CHAR(39)&TEXTJOIN(CHAR(39)&CHAR(59)&CHAR(39), TRUE, A1:A5)&CHAR(39)

A single tick (aka single quote or ') is ASCII character 39 and a semi-colon is ASCII character 59.

Excel VBA - String Conactenation with delimiter and single quotes

6 Answers6