Partial string search w/o VBA?
 

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.

Partial string search w/o VBA?
 

Layout & before/after examples

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MJW" wrote in message
...
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.

Partial string search w/o VBA?
 

What *exactly* are you looking for?

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MJW" wrote in message
...
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", 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.

Partial string search w/o VBA?
 

Verb

In article ,
"Don Guillett" wrote:

Layout & before/after examples

Partial string search w/o VBA?
 

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


"MJW" wrote in message
...
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.

Partial string search w/o VBA?
 

One way, perhaps:

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



In article ,
MJW wrote:

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.

Partial string search w/o VBA?
 

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

"RagDyer" wrote:

What *exactly* are you looking for?

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MJW" wrote in message
...
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", 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.