Search String for Return Value

[ilikebirds]

I once again come before you all to request assistance.

I have a lengthy String of Data Of which I would like to create new Columns for.

Example:
[Description] = "1, Bob, Brown, 58, Argyle, Big Jacket"

I would like to search that string for "Brown" and Return that value into a new Column called [Color].

"Brown" can appear anywhere in that string.

I have tried a combination of iif and instr to no success.

Color: IIf(InStr(Null,[Description],"Brown",1),"BROWN","magic")

Thank you

[ilikebirds]

In case others were wondering, I got it to work with:

Color: IIf(InStr([Description],"Brown")>1,"Brown","Miss")

It will return the Color into a new field.

However, this does not address the issues when another color is there, since the return values are Numerical.

Any ideas?

[DonRayner]

This should give you a starting point. It will ignore case but not spelling errors.

Dim vSource As Variant, vData As Variant
Dim sResult As String, sSource As String, sData As String
sSource = REPLACE
sData = "Blue, Brown, Green, Black"
For Each vSource In Split(sSource, ",")
    For Each vData In Split(sData, ",")
        If Trim(vSource) = Trim(vData) Then
            sResult = Trim(vSource)
        End If
    Next vData
Next vSource

Replace "REPLACE" with whatever your source is. You will have to enter all possible colours in sData or set sData = to whatever you use as the source for your colour list. Output will be sResult as a string.

[NeoPa]

I should warn you that searching in this way is fundamentally prone to problems and mistakes. Values of one type too often match values for another. In this case consider data for surnames and colour. How would you determine which it were if you found the value "Brown" in the data. This is a fundamentally poor way to manage data I'm afraid.

If this has been imposed upon you (often the case here with such questions), then you need to consider reporting back that this is inherently flawed and they will suffer ultimately if they persist in forcing this approach.

[ilikebirds]

DonRaynor, Thank You - I will give it a try.

---

NeoPa, I understand your concern as I addressed them myself. They have told me that they will validate their data beforehand. I have to try and make it work unfortunately.

Thanks - I will update will any results.

[OldBirdman]

If the search counts the number of times a color is found, and it is not 1, then the record should be rejected. This should address NeoPa's concern.

Color list will probably have to be a table, as list will go beyond the 11 simple colors to Teal, Peach, Scarlet, Forest, and anything else a sales dept. can come up with.

[DonRayner]

@OldBirdman
Also much easier to add another record to a lookup table rather than having to edit your VBA when they add a new color. You could then request an excel spreadsheet with all their possible colors listed and import the data your database as the lookup table.