Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Partial string search w/o VBA?
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Partial string search w/o VBA?
Verb
In article , "Don Guillett" wrote: Layout & before/after examples |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return partial string | Excel Worksheet Functions | |||
auto bold partial text in a string | Excel Discussion (Misc queries) | |||
Partial String Match Using VLOOKUP | Excel Worksheet Functions | |||
Partial String | Excel Discussion (Misc queries) | |||
Partial search and replace? | Excel Discussion (Misc queries) |