We help IT Professionals succeed at work.

Split comma separated strings into number values

TheUndecider asked

I have a SQL table with a field that stores strings that look like these:


As you can see, they start with a set of quotes (") followed by a string of numbers separated by a comma and ending in another set of quotes (").  Some of them have a set of 9 numbers in the string, others have more or less.

I need to split these strings and extract the numbers so they can be exported to Excel. I don't need the quotes or the commas to show.

Any ideas?


Watch Question

Top Expert 2010

Why not just export them to excel as is, and use excel's text to columns feature to separate them?
Here's some code to handle the string manipulation and output to Excel - of course, you'll need to modify this for your application. I wrote this in Access and just used a single row of data, but it would be easy to hook this up to a SQL table. It's not real fast, but it should give you an idea about how to proceed:
Option Compare Database
Option Explicit

Sub testSplit()
 Dim test As String
 Dim res As Variant
 Dim results() As String
 test = """1,2,3,4,5"""
 Debug.Print test
 test = Replace(test, """", "")
 Debug.Print test
 results() = Split(test, ",")
 For Each res In results()
    Debug.Print res
Call loadExcel(results())
End Sub

Sub loadExcel(results() As String)
    Dim myXL As Excel.Application
    Dim rg As Excel.Range
    Dim ws As Excel.Worksheet
    Dim wb As Excel.Workbook
    Dim res As Variant
    Set myXL = New Excel.Application
    myXL.Visible = True
   Set wb = myXL.Workbooks.Add
    Set rg = wb.Sheets("Sheet1").Range("A1")
    For Each res In results()
        rg.Value = res
        Set rg = rg.Offset(1, 0)
    wb.Close True
    Set myXL = Nothing
End Sub

Open in new window