Help Excel Formatting

[francesllee]

I have a word document that I want to convert to an excel file, that
looks like this:

Product: Apple
Price: $1.00
Amount: 2

Product: Orange
Price $2.00
Amount: 3

Product: Banana
Price: $0.50
Amount: 5

Product: Carrot
Price: $0.10
Amount: 10

I want to be able to convert it so it looks like this in excel:

Product Price Amount
Apple $1.00 2
Orange $2.00 3
Banana $0.50 5
Carrot $0.10 10


Please help! Thank you.

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Left(.Cells(i, "A").Value, 6) = "Amount" Then
.Cells(i - 2, "C").Value = _
Trim$(Replace$(.Cells(i, "A").Value, "Amount:", ""))
.Rows(i).Delete
ElseIf Left(.Cells(i, "A").Value, 5) = "Price" Then
.Cells(i - 1, "B").Value = _
Trim$(Replace$(.Cells(i, "A").Value, "Price:", ""))
.Rows(i).Delete
ElseIf Left(.Cells(i, "A").Value, 7) = "Product" Then
.Cells(i, "A").Value = _
Trim$(Replace$(.Cells(i, "A").Value, "Product:", ""))
ElseIf .Cells(i, "A").Value = "" Then
.Rows(i).Delete
End If
Next i
.Rows(1).Insert
.Range("A1").Value = "Product"
.Range("B1").Value = "Price"
.Range("C1").Value = "Amount"

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)