How to switch data between rows and columns by using programming?

  • Thread starter Thread starter Fang
  • Start date Start date
F

Fang

I have text file like following:
NAME: Mike
price: $12
UNIT: 3
NAME: Tom
price: $12
UNIT: 3
NAME: SMITH
PRICE: $16
UNIT: 6
NAME: Jone
price: $22
UNIT: 3
NAME: Yang
price: $32
UNIT: 5
NAME: Yubin
PRICE: $6
UNIT: 10

How to switch to Excel file like following style?

NAME price UNIT
Mike $12 3
Tom $12 3
SMITH $16 6
Jone $22 3
Yang $32 5
Yubin $6 10

thank you!
 
You could put the text file data in column A of Sheet1
and then run this macro:

Sub macro1()
Dim iRow As Integer
Dim iRow2 As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
ws.Range("C1") = "NAME"
ws.Range("D1") = "PRICE"
ws.Range("E1") = "UNIT"
iRow = 3
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(1 + iRow / 3, 3) = Right(ws.Cells(iRow - 2, 1),
Len(ws.Cells(iRow - 2, 1)) - 6)
ws.Cells(1 + iRow / 3, 4) = Right(ws.Cells(iRow - 1, 1),
Len(ws.Cells(iRow - 1, 1)) - 7)
ws.Cells(1 + iRow / 3, 5) = Right(ws.Cells(iRow, 1),
Len(ws.Cells(iRow, 1)) - 6)
iRow = iRow + 3
Loop
End Sub

HTH,
Merjet
 
Back
Top