Complicated extraction of text

L

lohwk

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 are:
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
 
D

Don Guillett

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
 
R

Ron Rosenfeld

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 are:
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
Click to expand...

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")&RIGHT(A1,1),"")

Copy/drag down as far as necessary.


--ron
 
Top