Dave

I tried that and it's fine down to the missing Item# and then goes haywire
again. (Put's the $ in position 2).bizarre...

Sure it works for most though as OP hasn't returned with an issue

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
Maybe better:

ElseIf LCase(trim(.Cells(iRow, "A").Text)) Like "$*" Then

to eliminate that extra space.

===
I'm betting that that currency/accounting style does that to align stuff
in the
cell.

(I don't have any money, so it's not something I usually deal with <vbg.)

Nick Hodge wrote:

Dave

Update...

It appears the accounting format I use has some padding around the $.
I'll
take another look.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

"Nick Hodge" wrote in message
...
Dave

For some reason this doesn't work for me. I tried debugging and the
code
that picks up the $* doesn't seem to pick it up, even though you are
using
the text property which in the immediate window clearly shows the
leading
$.

Any thoughts?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
This worked for me in xl2003:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oRow = oRow + 1
oCol = 1
ElseIf LCase(.Cells(iRow, "A").Text) Like "$*" Then
oCol = 3
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin
with
the word "Item" in column A, the item names in column B, and the
prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

--

Dave Peterson



--

Dave Peterson