CODEWITHSUNDEEP
excel
ES59
ES59

Reputation: 3

Excel one row to many, based on column data

I've got an Excel file with over 10k rows, which looks like

ID |DATA1| DATA2

1 |Text | 13;15;16

2 |Text2 |13;12

I need to expand numbers in DATA2 field using delimeter ";" to a new rows:

ID|DATA1|DATA2

1 | Text| 13

1 | Text| 15

1 | Text| 16

2 | Text2| 13

2 | Text2| 12

Is it possible to do it with Excel?

Upvotes: 0

Views: 77

Answers (1)

aimprogman
aimprogman

Reputation: 294

You can do it with VBA. Open the Macros in MS Excel and insert this code. 

Sub todo()
 lRow = Cells(Rows.Count, 1).End(xlUp).Row
 rowNum = lRow
   For r = 1 To lRow
    symbolCounter = UBound(Split(Cells(r, 3).value, ";")) + 1
    textVal = Split(Cells(r, 3).value, ";")
      For c = 1 To symbolCounter
        rowNum = rowNum + 1
        Cells(rowNum, 1).value = Cells(r, 1).value
        Cells(rowNum, 2).value = Cells(r, 2).value
        Cells(rowNum, 3).value = textVal(c - 1)
      Next c
   Next r
End Sub

Should work.I checked.

Upvotes: 1

Related Questions