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

I had to clean up some code a little to make it more readable (use indenting when nesting code in other blocks of code, got rid of cosmetic/performance stuff)...

Sub SEPARATE()
    Dim r As Long [COLOR=#008000]'use Long for row numbers, modern computers dont need to consider memory usage of 16 vs 32 bit number and Integer cannot accomodate all row values on xlsx files even though you only max the value at 100[/COLOR]
    Dim sheet As Worksheet
    Dim raw, qty, part, titan, description As String
    Dim splitArray() As String


    For Each sheet In Worksheets
        sheet.Activate


        For r = 5 To 100 'Continue loop until row 100
            raw = Cells(r, 4).Value
            splitArray = Split(raw, " ")
            qty = splitArray(0)
            part = splitArray(1)
            titan = splitArray(2)
            description = Mid(raw, Len(qty) + Len(part) + Len(titan) + 3)

            [COLOR=#008000]'concatenate qty, part, titan and description anyway you like and set to whatever cells you want[/COLOR]
        Next r
    Next sheet
End Sub

 

[engineerseay]

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

Thanks for the help! Could you elaborate a bit? I am trying to learn but have already spent a fair amount of time on it. when I try to run the code it gives me a "subscript out of range" when it reaches qty= splitarray(0). Also, some info I forgot to include, every once in awhile there will be a line that has a variance, such as:

2 P007806 CUT FROM 11237 SKIN, COLONIAL, 6 PANEL, 1/0 X 8/0

<tbody>
</tbody>

this would need to be split up like so:

2; P007806; CUT FROM 11237; (SKIN, COLONIAL, 6 PANEL, 1/0 X 8/0

<tbody>
</tbody>

)

Which is why I tried to split the first few sets of data via " " and the last via "S" - because the last column text will always start with a S.

Once it splits the array, how do I set the data to a cell?

Thanks

 

[engineerseay]

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

I am pretty sure I know how to set the data from the split.... {Cells(x,y).value = qty} for example?

 

[engineerseay]

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

I had to clean up some code a little to make it more readable (use indenting when nesting code in other blocks of code, got rid of cosmetic/performance stuff)...

Sub SEPARATE()
    Dim r As Long [COLOR=#008000]'use Long for row numbers, modern computers dont need to consider memory usage of 16 vs 32 bit number and Integer cannot accomodate all row values on xlsx files even though you only max the value at 100[/COLOR]
    Dim sheet As Worksheet
    Dim raw, qty, part, titan, description As String
    Dim splitArray() As String


    For Each sheet In Worksheets
        sheet.Activate


        For r = 5 To 100 'Continue loop until row 100
            raw = Cells(r, 4).Value
            splitArray = Split(raw, " ")
            qty = splitArray(0)
            part = splitArray(1)
            titan = splitArray(2)
            description = Mid(raw, Len(qty) + Len(part) + Len(titan) + 3)

            [COLOR=#008000]'concatenate qty, part, titan and description anyway you like and set to whatever cells you want[/COLOR]
        Next r
    Next sheet
End Sub

Sub SEPARATE()
    Dim r As Long 'use Long for row numbers, modern computers dont need to consider memory usage of 16 vs 32 bit number and Integer cannot accomodate all row values on xlsx files even though you only max the value at 100
    Dim sheet As Worksheet
    Dim raw, qty, part, titan, description, Remain As String
    Dim splitArray() As String
    Dim splitArray2() As String


    Application.ScreenUpdating = False
    For Each sheet In Worksheets
        sheet.Activate




        For r = 5 To 100 'Continue loop until row 100
            raw = Cells(r, 4).Value
            splitArray = Split(raw, " ")
            qty = splitArray(0)
            part = splitArray(1)
            Remain = Mid(raw, Len(qty) + Len(part) + Len(titan) + 1)
            Cells(r, 4).Value = qty
            Cells(r, 5).Value = part
            
            splitArray2 = Split(Remain, "S")
            titan = splitArray2(0)
            description = Mid(Remain, Len(titan) + 1)
            
            Cells(r, 6).Value = titan
            Cells(r, 7).Value = discreiption
            
        Next r
        
    Columns.AutoFit
    Application.ScreenUpdating = True
    Next sheet
End Sub
[/QUOTE][/QUOTE]

 

[engineerseay]

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

Would my code look like that? (attached in the response above). I am still getting a "Subscript out of range" error at qty= splitArray(0)

 

[Rick Rothstein]

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

Does this macro do what you want...

Sub SEPARATE()
  Dim Cell As Range
  For Each Cell In Range("D5", Cells(Rows.Count, "D").End(xlUp))
    Cell.Resize(, 4) = Split(Cell.Value, " ", 4)
  Next
End Sub

 

[engineerseay]

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

Very nice! Extremely close to what I need! but there are a few lines like such :

2 P007806 CUT FROM 11237 SKIN, COLONIAL, 6 PANEL, 1/0 X8/0

<tbody>
</tbody>

instead of

2 P011954 88135 SKIN, COLONIST, TEXTURED, 6 PANEL, 3/0 X 8/0


So I need a way that will keep the entire "CUT FROM 11237" in one column. Which is why I was trying to separate the first 2 columns via a space, and the next two columns via the "s" character. Because the 4th column will always have text starting with "skin".

 

[Rick Rothstein]

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

Very nice! Extremely close to what I need! but there are a few lines like such :

2 P007806 CUT FROM 11237 SKIN, COLONIAL, 6 PANEL, 1/0 X8/0

<tbody>
</tbody>

instead of

2 P011954 88135 SKIN, COLONIST, TEXTURED, 6 PANEL, 3/0 X 8/0

Does this revised code do what you want...

[table="width: 500"]
[tr]
	[td]Sub SEPARATE()
  Dim Cell As Range
  For Each Cell In Range("D5", Cells(Rows.Count, "D").End(xlUp))
    Cell.Resize(, 3) = Split(Cell.Value, " ", 3)
    Cell.Offset(, 2).Resize(, 2) = Split(Replace(Cell.Offset(, 2), " S", " SS", , 1), " S", 2)
  Next
End Sub[/td]
[/tr]
[/table]

 

[engineerseay]

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!