Complicated extraction of text

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

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
[email protected]
"lohwk" <[email protected]> wrote in message
news:[email protected]...
> 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

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
[email protected]
"lohwk" <[email protected]> wrote in message
news:[email protected]...
> 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

On Thu, 25 May 2006 05:49:01 -0700, lohwk <[email protected]>
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 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

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

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
> [email protected]
> "lohwk" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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
> [email protected]
> "lohwk" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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
> [email protected]
> "lohwk" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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
[email protected]
"lohwk" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> "lohwk" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

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
> [email protected]
> "lohwk" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >> [email protected]
> >> "lohwk" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>
>

A simple matter of changing the range variable to suit

--
Don Guillett
SalesAid Software
[email protected]
"lohwk" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> "lohwk" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >> [email protected]
>> >> "lohwk" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

Hi Don

I think that your suggestion here will also solve a problem that I am having. One question fro you though, is it possible to set the array so that is it dynamic and 'linked' to a list of text?

By this I mean instead of having
myarray = Array("BLUE", "RED", "GREEN", "BROWN")
within the script, to have the words blue, green, brown etc as a list on a worksheet ? If so how?

Thanks

Karen