Separating a Variant "String" into separate columns

[engineerseay]

I have a large amount of data to separate into columns. I am fairly new to VBA, so I need help figuring it out. I have done a fair amount of searching which led to many failed attempts.


So the current column data looks like this: 2 P011954 88135 SKIN, COLONIST, TEXTURED, 6 PANEL, 3/0 X 8/0

I want to separate it into 4 different columns. 2, P011954, 88135, & (SKIN, COLONIST, TEXTURED, 6 PANEL, 3/0 X 8/0) remaining.

currently this is my code: (cant figure out how to add a screen shot) I know it is not really doing anything at the moment.

'** PROGRAM PURPOSE:SEPARATE QTY, PART #, TITAN #, AND DISCRIPTION INTO SEPERATE CELLS.
'** INPUTS/OUTPUTS: STING OF DATA/ SEPARATED DATA
'** NOTES:
'**
'**************************************************************************************************
Sub SEPARATE()
    Dim I As Integer
    Dim qty, part, titan, discription, J, n, x As String
    
    Application.ScreenUpdating = False
    For Each Sheet In Worksheets
        Sheet.Activate
            
            I = 5                                  'I is used to indentify starting row.
            
            Columns.AutoFit
        
            For I = 5 To 100         'Continue loop until row 100
                J = Cells(I, 4).Value
                qty = Split(J, " ", 2)
            
                n = Cells(I, 5).Value
                part = Split(n, " ", 2)
                'ActiveCell.Resize(1, UBound(part) + 1).Offset(0, 1) = part   -Tried this and it did not work
                
                x = Cells(I, 6).Value
                titan = Split(x, "S", 2)
                'ActiveCell.Resize(1, UBound(titan) + 1).Offset(0, 1) = titan
                        
                
            Next I
                            
            
    Columns.AutoFit
    Next Sheet
    Application.ScreenUpdating = True
    
    
End Sub

 

[cerfani]

Re: Separating a Variant "String" into separate columns - NEED HELP PLEASE & THANKS

THAT WORKED PERFECT! Thank you!!!! I attempted to slightly modify your first code, which sort of worked, but it deleted the work "skin" from each description. The code you just posted is exactly what I was looking for!


Thanks Agian!

Sorry, didnt see this until now. But the code i modified for you will only grab the first 3 values seperated by blanks and then the 4th value will be whatever is left. Since your values did not follow the format from the example then it will not work for values that have a space in the parsed values themselves. It looks like you should just use the the solution posted by Rick.

Also regarding the subscript being out of range, might be because the array returned from the split function uses base index 1 instead of 0. Although when I tested it, it worked fine so perhaps you are setting the option for arrays to be based at 1 but i doubt that. Your code is probably a little different... regardless, go with the other solution.

 

[Rick Rothstein]

Re: Separating a Variant "String" into separate columns - NEED HELP PLEASE & THANKS

Also regarding the subscript being out of range, might be because the array returned from the split function uses base index 1 instead of 0.

The lower bound for an array created by the Split function is always 0 (even if you are using Option Base 1).