I have an excel file which I'll use to import hundreds of products in an e-shop. The excel have columns like this

Title | SKU | Size | Price | etc

I asked my clients to make the excel in very specific format (especially on rows). I wanted each variation of the product (size) to has its own row. Lets say for a shirt which has 3 sizes (S, M, L), it should have 3 rows for each variation. Well, they sent me the excel with the variations to be in one row, like this


I'm totally noob on Excel. How can I make a macro lets say, to check for the variations which are seperated by the slash and make x number of duplicated rows? And also if (that would make me veeeery happy) can keep only one variation in each row.

Thank you :)


Right click on sheet's name and pick "Show code".

Paste the following code:

Sub Expandrows()
Dim sizes() As String
For Each c In Selection
    With c
        If .Value <> "" Then
            sizes = Split(.Value, "/")
            If UBound(sizes) > 0 Then
                For i = UBound(sizes) To LBound(sizes) Step -1
                    Rows(.Row + 1).Insert shift:=xlDown
                    .Offset(1) = sizes(i)
                Next i
                Application.CutCopyMode = False
            End If
        Else: Exit For
        End If
    End With
Next c
End Sub

Go back to your sheet, select the "Size" column by clicking on its header.

Under the "Developer" menu, run macro from "Macros".

| improve this answer | |
  • Well... It just works!! I did it with python and CSV files but your method it's just what I needed! Thank you – hambos22 Nov 13 '15 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.