Surround values with single quote

bjcf33183

New Member
Joined
Dec 11, 2017
Messages
8
Hi,
I created the below sub to convert a single column of values (of varying amounts) into a single, comma separated string.
Thus if Column A on "Sheet1" contains:

50
55
60
65
70
75

The procedure will convert that to a comma separated string on a new worksheet ("List1"), e.g.:
50, 55, 60, 65, 70, 75

What I'm *attempting* to do is tweak that slightly so that each value in the string is surrounded by a single quote, e.g.:
'50', '55', '60', '65', '70', '75'.

Below is the current procedure.

*NOTE.. I tried updating this portion :

outStr = outStr & "," & rng.Value

to this: (added the stuff in red)

outStr = "'" & outStr & "," & rng.Value & "'"

..but it didn't quite turn out right.

=============================================================

Code:
Sub ConvertColunmToCommaString2()
'Takes the list of items in Column A on "Sheet1" and converts that list to a single comma separated string on a worksheet called "List1".
'The purpose of this is to translate a list of items (user PK Numbers for example) into a string of values that can be used on a SQL script
'e.g.: DELETE COLUMN_1 FROM TABLE_1 WHERE COLUMN_2 IN('Value [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] ', 'Value [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] ', 'Value [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3"]#3[/URL] ', etc.)


'Declare Variables
Dim LastRow As Long
Dim TargetRange As Range
Dim Outrng As Range
Dim rng As Range
Dim ws1, ws2 As Worksheet
Dim wb As Workbook
Dim outStr As String
Dim DoesWs2Exist As Boolean


'Step 1: Add a new worksheet to paste the comma string of DB's into

Sheets.Add  
ActiveSheet.Name = "List1"



'Set Worksheet Variables
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("List1")


'Set "Last Row" value to the number of rows populated with a value in Column A (on Active Sheet)
LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).row


'Set Input Range = ALL items in Column A on the Active Worksheet
Set TargetRange = ws1.Range("A1:A" & LastRow)


'Set Destination Range = Cell A1 on the second worksheet ("List1")
Set Outrng = ws2.Range("A1")


outStr = ""  'Set output string to null prior to below statement


'For each item in Target (Input) Range, aka Column A on the Active Worksheet..
For Each rng In TargetRange
    If outStr = "" Then
        outStr = rng.Value
    Else
        outStr = outStr & "," & rng.Value
    End If
Next
Outrng.Value = outStr


End Sub
 
Last edited by a moderator:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try:
Code:
    If outStr = "" Then
        outStr = "'" & rng.Value & "'"
    Else
        outStr = outStr & ",'" & rng.Value & "'"
    End If
 
Upvote 0
Here is a UDF (user defined function), which can be called from other VB code if desired, that will take a vertical, contiguous range of cells and output the text string that you indicated you wanted...
Code:
[table="width: 500"]
[tr]
	[td]Function Apostrophed(VerticalRange As Range) As String
  Apostrophed = "'" & Join(Application.Transpose(VerticalRange), "', '") & "'"
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Apostrophed just like it was a built-in Excel function. For example,

=Apostrophed(A1:A6)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top