You need to put it in a regular module. Not in the sheet module or the
ThisWorkbook module. Then you need to modify to suit your range.
something like
For Each c In Range("a2:a" & cells(rows.count,"a").end(xlup).row)
Then you need to assign to a button or shape or just use alt f8 to get to
the macros.

If after you have done all this and still can't get it send me a sample
workbook to the addy below.


--
Don Guillett
SalesAid Software

"lohwk" wrote in message
...
Hi Don,

Thanks for your reply. I pasted the code, but how to i use it?

"Don Guillett" wrote:

Part 1 and 2
dddd
BLUE GC 201c BLUE 201C
RED GW 23c RED 023C
GREEN9c GREEN 009C
GCBROWN12c BROWN 012C



Sub ExtractColorsandNumbers()
myarray = Array("BLUE", "RED", "GREEN", "BROWN")
For Each c In Range("a2:a6")
For Each i In myarray
If InStr(c, i) 0 Then 'MsgBox c.Address
c.Offset(, 1) = i
End If
Next i
For j = 1 To Len(c.Value)
If Mid(c.Value, j, 1) Like "*[0-9]*" Then
ms = Right(c, Len(c) - j + 1)
c.Offset(, 2) = _
UCase(Application.Rept("0", 4 - Len(ms)) & ms)
Exit For
End If
Next j
Next c
End Sub

--
Don Guillett
SalesAid Software

"lohwk" wrote in message
...
Hi Everyone,

I have a column of cells containing descriptions of some items (in
Sheet1,
Cell B2 downwards), that looks like this:

BLUE GC 201c
RED GW 23c
GREEN9c
GCBROWN12c

I need to extract the cell and put my results in 2 fields.

The 1st (result) field should have value like:
BLUE
RED
GREEN
BROWN

For the above results to appear, i have to lookup on whether these
colors
exist in Sheet2!A1:A12

For the second (result) field, the expected fields a
201C
023C
009C
012C

As you can see, i can't think of any logic to cater for this,
especially
to
get the second result because the description contains too many
variable
instances.

Any help would be appreciated