Creating a new line for every / and keeping pertinent info RRS feed

  • Question

  • It is hard to describe in the short post title exactly what I'm trying to do but perhaps showing you is easier. Let me try to describe the problem and I'll provide an example...

    I have a data set of vehicles. For any model that has sub-models they are listed with slashes, but I need them on new lines. However I don't just need the information between the slashes broken out, which I can do, I need to keep part of the cell entry combined with that part of the entry picked from the slashed stuff. The row also includes other information pertinent to the models combined on the row in cells before and after the entry to be split so I need to copy that for each new entry.

    So for example I have:

    Desired Result

    Can someone help me figure out how to make this happen. I have used VBA in Access a bit but not a whole lot in excel. (Alternatively if there is a formulaic way that is not completely insane I'd be interested to know).

    Thank you for the help!


    Thursday, February 2, 2017 6:47 PM

All replies

  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    No formulas, no VBA macro.
    Used calendar instead of cars to maximize utility to community.

    Thursday, February 2, 2017 9:14 PM
  • Hi Doug Comstock,

    I can see that the changes are only in sub models column and all other column data will remain same.

    so here I just try to focus on sub models and try to give you an example.


    Sub demo()
    Dim txt As String
    Dim i As Integer
    Dim fullname As Variant
    txt = ActiveCell.Value
    fullname = Split(txt, "/")
    Debug.Print (UBound(fullname))
    For i = 1 To UBound(fullname)
    Cells(i + 1, 1).Value = fullname(0) & fullname(i)
    Next i
    End Sub

    this is just a logic how to split the data.

    to fulfill your requirement.

    you need to loop through all the rows in your sheet.

    and you need to specify where you want to store the result. in the same sheet or in different sheet.

    because here for testing we just display the result in following rows.

    with that you need to copy same data for other columns too.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, February 3, 2017 1:57 AM