Hi Ron,

we're getting really close. the unique string can be anywhere in the
searched strings. however, the desired return may not contain something in
the unique string.

Col A desired return
william jones bill
bill smith bill
will robins bill
jon smith jon
d. robert burns rob
rob miller rob

so i need a translator in there as well, i could set up the "uniques" as you
suggest, but i'd need a second so that i would have

Col A uniques_ID uniques_return
william jones william bill
bill smith bill bill
will robins will bill
jon smith jon jon
d. robert burns robert rob
rob miller rob rob


so it would search Col A for the Uniques_ID string and if it finds it it
would then return the Uniques_return...

is that a possible variation on your suggestion?

Thanks!

Jonathan

"Ron Rosenfeld" wrote:

On Tue, 19 Jun 2007 15:28:18 -0700, Jonathan Horvath
wrote:

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

If the unique strings can be found anywhere in the searched strings, you could
use this **array** formula.

First, set up a range where you list your 10 different unique strings. I
NAME'd it "Uniques".

Then, assuming your strings to search start in A2, enter the formula below as
an array formula. That means to hold down <ctrl<shift while you hit <enter.
Excel will place braces {...} around the formula

=IF(OR(ISNUMBER(FIND(Uniques,A2))),
INDEX(Uniques,MATCH(TRUE,(ISNUMBER(
FIND(Uniques,A2))),0)),"")

Then fill down the 2000 entries.

If the unique strings are required to be at the beginning of the search string,
I'd probably use a UDF.
--ron