Partial string search w/o VBA?

[Guest]

Hi All,

As useful as the MID, LEFT and RIGHT functions are for FIND, I'm having an
issue which I hope isn't much of one at all. I'm looking to validate a
certain text value inside a adjacent cell, but the problem is that it can
fall in multiple locations in the cell because of how the the processor
enters the data (IE: "1-Year Self-Maintenance", "1 Year Self-Maintenance",
"1-Year Self Maintenance", etc...); the differences are subtle, but enough to
throw off a static search. Is there an easy way to perform this without
coding it in VBA? Heck, is there ANY way to do this without VBA? I'm
looking at approximately 60 possible combinations of that kind of string (not
only is there a formatting concern, but the numerical value can equal 1-5 as
well), and Excel would laugh at me if I even TRIED a formula with that many
nested IF's and OR's. Any help would be greatly appreciated.

Thanks

Jamie W.

 

[Don Guillett]

Layout & before/after examples

 

[RagDyer]

What *exactly* are you looking for?

I know you're not talking about 60 variations of that single phrase;
"1-Year Self-Maintenance"

 

[JE McGimpsey]

Verb

 

[Bernie Deitrick]

Jamie,

You could compare to a table of allowed variants:

=ISNUMBER(MATCH(A1,AllowedVariants,FALSE))

will return true if A1 contains any of the values listed in the range AllowedVariants (a named range
that can be as big as you want)


HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

One way, perhaps:

=FIND("1YearSelfMaintenance", SUBSTITUTE(SUBSTITUTE(A1, " ", ""),
"-", ""))

 

[Guest]

RD,

Not that particular phrase--the "60" comes in because the numerical value
can be 1-5. With the variations already existing with dashes and spacing, it
comes to 60. Looks like JE had the answer though--I absolutely blanked on
the substitute function.

Thanks

Jamie