Text string to database conversion

Chris646 · Mar 13, 2019

Hi All-

Is there a way to convert a spreadsheet that has a text string of data in one field to multiple input rows? In the attached example I in the Input tab I show the data that I currently have and how it is inputed. In the output tab I show how I would like the data to be presented in order to create a database type file. Is it possible to write a MACRO to do this automatically? Please advise. Thanks in advance.

NoS · Mar 13, 2019

try this

Code:

Sub Chris646()
    Dim lr As Long, i As Long, x As Long
    Dim ray As Variant
With Sheets("Input")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        If InStr(.Cells(i, "B"), ",") <> 0 Then
            ray = Split(.Cells(i, "B").Value, ",")
            x = UBound(ray)
            .Rows(i + 1).Resize(x).Insert
            For x = LBound(ray) To UBound(ray)
                .Cells(i + x, "A") = .Cells(i, "A")
                .Cells(i + x, "B") = ray(x)
            Next x
        End If
    Next i
End With
End Sub

Text string to database conversion

Chris646

New member

Attachments

NoS

Member