Separating data with multiple delimiters into columns AND rows

calaloo722

New Member
Joined
Apr 8, 2013
Messages
12
Hello,
I have a column within a spreadsheet that has data separated by 2 delimiters, a "\" and a "/". (This data has been extracted from a linux-based file.)

For example: "1\100789/3\200899/6\xlm-sgt-455-0987"

The items of data are from a bill of material (parts explosion) and the number preceding the "\" is a quantity and the numbers preceding the "/" is a sub-part number of the main part number that is entered into a column to the left of this data string. (unseen in the example)

I need to take this string of numbers and place the quantity in one column, the part number in another column, then add a row and continue to populate each column until the data has all been separated, then move on to the next main part number row to continue the process.

for example:
1 100789
3 200899
6 xlm-sgt-455-0987

Is this possible with the data tools in a spreadsheet, or will I need to write a macro? If a macro is needed, I'll need help with the code.

Thank you!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try this. It assumes the data is in column A on Sheet1 starting at A1 and it puts the results in columns A-B on Sheet2.
Code:
Sub sep()

    Dim r As Long, n As Long, parts As Variant
    
    n = 1
    For r = 1 To Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
        parts = Split(Cells(r, "A"), "/")
        Sheets("Sheet2").Cells(n, "A").Resize(1, 2).Value = Split(parts(0), "\")
        Sheets("Sheet2").Cells(n + 1, "A").Resize(1, 2).Value = Split(parts(1), "\")
        Sheets("Sheet2").Cells(n + 2, "A").Resize(1, 2).Value = Split(parts(2), "\")
        n = n + 3
    Next
    
End Sub
 
Upvote 0
John_w,
The code works for separating the data into cells & rows, thank you! But I need the sub parts to follow the main part number. My data is saved on the spreadsheet as thus:

Main Part Number Description Sub-Parts
301000 Box 1\200546/3\722833/2\407532/4\crs-360-750
222000 Handle 1\34417-249/1\290876/1\zfg-wht-002-2250
100550 Knob .076\s-rod-034-0075

So, while the code you gave me works to break down the "sub-parts" into their own rows, I need them to stack next to and below their main part numbers, too. Like this:

301000 Box 1 200546
301000 Box 3 722833
301000 Box 2 407532
301000 Box 4 crs-360-750
222000 Handle 1 34417-249
222000 Handle 1 290876
222000 Handle 1 zfg-wht-002-2250
100550 Knob s-rod-034-0075

or like this:
301000 Box 1 200546
................3 722833
................2 407532
................4 crs-360-750
222000 Handle 1 34417-249
....................1 290876
....................1 zfg-wht-002-2250
100550 Knob .076 s-rod-034-0075 (ignore the dot leaders, they're place holders for the post only!!)

Thanks! And sorry for my incomplete description originally!
 
Last edited:
Upvote 0
Try this then. It takes the data in columns A and B on Sheet1 and puts it in A-C on Sheet2.
Code:
Sub sep2()

    Dim r As Long, n As Long, parts As Variant, i As Long
    
    Sheets("Sheet2").Cells.ClearContents
    n = 1
    For r = 1 To Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
        parts = Split(Sheets("Sheet1").Cells(r, "B"), "/")
        For i = 0 To 2
            With Sheets("Sheet2")
                .Cells(n + i, "A").Value = Sheets("Sheet1").Cells(r, "A").Value
                .Cells(n + i, "B").Value = Split(parts(i), "\")(0)
                .Cells(n + i, "C").Value = Split(parts(i), "\")(1)
            End With
        Next
        n = n + 3
    Next
    
End Sub
 
Upvote 0
I need the data in columns A & B to remain in their respective columns. The delimited data are all in column C. I need the data broken into 2 columns, then rows until all the data is broken down. Then columns D thru G need to remain in their respective columns but shifted over one to accommodate the separated data from column C.

Could you please tell me how to insert my examples because I don't think I'm explaining this very well!

Thanks!
 
Upvote 0
After 2 attempts at trying to understand your data layout - which you seem to be revealing bit by bit with every post - I'm obviously missing something, so maybe someone else can help on this. Use one of the board's HTML makers to post the data or upload your workbook to a file sharing site.
 
Upvote 0
I was hoping I would understand your code and be able to manipulate it to work for me, but that is not the case. Sorry.
I've uploaded two workbook examples to 4shared.com - free file sharing and storage
The links are:
http://www.4shared.com/file/86KDoyAp/Unfixed.html which shows a portion of my large spreadsheet as it is now.

http://www.4shared.com/file/9t-xJ-O0/Fixed.html shows what I'm trying to accomplish.


I'm sorry this has been frustrating for you, it's just very difficult to put into words ONLY what I'm trying to do.
Thanks for your patience!!
 
Last edited:
Upvote 0
3rd time lucky maybe...
Code:
Sub sep3()

    Dim r As Long, n As Long, parts As Variant, i As Long
    Dim inputSheet As Worksheet, outputSheet As Worksheet
    
    Set inputSheet = Sheets("Sheet1")
    Set outputSheet = Sheets("Sheet2")
    
    outputSheet.Cells.ClearContents
    n = 1
    For r = 1 To inputSheet.Range("A1").CurrentRegion.Rows.Count
        With outputSheet
            .Cells(n, "A").Value = inputSheet.Cells(r, "A").Value
            .Cells(n, "B").Value = inputSheet.Cells(r, "B").Value
            parts = Split(inputSheet.Cells(r, "C"), "/")
            For i = 0 To UBound(parts) - 1
                .Cells(n + i, "C").Value = Split(parts(i), "\")(0)
                .Cells(n + i, "D").Value = Split(parts(i), "\")(1)
                .Cells(n + i, "E").Value = Split(parts(i), "\")(2)
            Next
            .Cells(n, "F").Value = inputSheet.Cells(r, "D").Value
            .Cells(n, "G").Value = inputSheet.Cells(r, "E").Value
            .Cells(n, "H").Value = inputSheet.Cells(r, "F").Value
            .Cells(n, "I").Value = inputSheet.Cells(r, "G").Value
            n = n + i
        End With
        
    Next
    
End Sub
The data is expected to be on the sheet named "Sheet1" and it puts the rearranged data on "Sheet2" - change these strings in the code if these aren't the correct sheet names.
 
Upvote 0
SUCCESS!!! (at least with the test page!!) Thank you, thank you!!!

I'll update when I have a chance to test "the big file"

Thanks again!!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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