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

Part 1
Sub extractcolors()
myarray = Array("BLUE", "RED", "GREEN", "BROWN")
For Each c In Range("a2:a6")
For Each i In myarray
If InStr(c, i) 0 Then c.Offset(, 1) = i
Next i
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

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

On Thu, 25 May 2006 05:49:01 -0700, lohwk
wrote:

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

From what you write, I am assuming you want a BLANK if there is nothing in the
first string that appears in Sheet2!A1:A12.

I am also assuming you want your number to end with the same ending as the
original data. In the examples this was a "c", but possibly could be other
characters.

Here's one way of doing it:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

With your data on Sheet1!A1:An, use the following "regular expression"
formulas:

C1: =REGEX.MID(A1,MCONCAT(Sheet2!$A$1:$A$12,"|"))
D1: =IF(LEN(C1)0,TEXT(REGEX.MID(A1,"\d+"),"0000")&RIG HT(A1,1),"")

Copy/drag down as far as necessary.


--ron

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

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

Hi Don,

I did as you told me to (pasting the code into a regular module), but i'm
still not sure whether i'm doing it right because i am not familiar with VB.
When i ran it (alt+F8), the results that appeared didn't come out as i
expected it to be. I sent you an email with my workbook as an attachment,
could you please have a look at it?

Thanks

"Don Guillett" wrote:

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

A simple matter of changing the range variable to suit

--
Don Guillett
SalesAid Software

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

I did as you told me to (pasting the code into a regular module), but i'm
still not sure whether i'm doing it right because i am not familiar with
VB.
When i ran it (alt+F8), the results that appeared didn't come out as i
expected it to be. I sent you an email with my workbook as an attachment,
could you please have a look at it?

Thanks

"Don Guillett" wrote:

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