Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

What version of Excel are you using? Versions prior to XL 07 have a
limit of 7 nested functions.

Dave

On Sep 20, 3:26 pm, MJW wrote:
Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)="yr", if(or(left(p2,7)="ASP1 yr", left(p2,7)="ASP 1yr",
left(p2,8)="ASP 1 yr"), "A1", if(or(left(p2,9)="Basic1 yr", left(p2,9)="Basic
1yr", left(p2,10)="Basic 1 yr"), "CC1", if(or(left(p2,11)="Premium1 yr",
left(p2,11)="Premium 1yr", left(p2,12)="Premium 1 yr"), "CC+1",
if(or(left(p2,7)="ASP3 yr", left(p2,7)="ASP3 yr", left(p2,8)="ASP 3 yr"),
"A3", if(or(left(p2,9)="Basic 3yr", left(p2,9)="Basic3 yr",
left(p2,10)="Basic 3 yr"), "CC3", if(or(left(p2,11)="Premium 3yr",
left(p2,11)="Premium3 yr", left(p2,12)="Premium 3 yr"), "CC+3",
if(or(left(p2,7)="ASP5 yr", left(p2,7)="ASP 5yr", left(p2,8)="ASP 5 yr"),
"A5", if(or(left(p2,9)="Basic 5yr", left(p2,9)="Basic5 yr",
left(p2,10)="Basic 5 yr"), "CC5", if(or(left(p2,11)="Premium5 yr",
left(p2,11)="Premium 5yr", left(p2,12)="Premium 5 yr"), "CC+5"))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Before I spend a load of time scanning through it, can you explain in
words what it is trying to do and in what way it is failing - error?,
unexpected results? etc

Pete

On Sep 20, 8:26 pm, MJW wrote:
Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)="yr", if(or(left(p2,7)="ASP1 yr", left(p2,7)="ASP 1yr",
left(p2,8)="ASP 1 yr"), "A1", if(or(left(p2,9)="Basic1 yr", left(p2,9)="Basic
1yr", left(p2,10)="Basic 1 yr"), "CC1", if(or(left(p2,11)="Premium1 yr",
left(p2,11)="Premium 1yr", left(p2,12)="Premium 1 yr"), "CC+1",
if(or(left(p2,7)="ASP3 yr", left(p2,7)="ASP3 yr", left(p2,8)="ASP 3 yr"),
"A3", if(or(left(p2,9)="Basic 3yr", left(p2,9)="Basic3 yr",
left(p2,10)="Basic 3 yr"), "CC3", if(or(left(p2,11)="Premium 3yr",
left(p2,11)="Premium3 yr", left(p2,12)="Premium 3 yr"), "CC+3",
if(or(left(p2,7)="ASP5 yr", left(p2,7)="ASP 5yr", left(p2,8)="ASP 5 yr"),
"A5", if(or(left(p2,9)="Basic 5yr", left(p2,9)="Basic5 yr",
left(p2,10)="Basic 5 yr"), "CC5", if(or(left(p2,11)="Premium5 yr",
left(p2,11)="Premium 5yr", left(p2,12)="Premium 5 yr"), "CC+5"))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Without a doubt there's an easier way of handling this than what you've
tried. Rather than forcing us to try to decipher this monster, how about a
simple explanation of your objective and we'll try to get you started down
the right path

"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Hi Pete,

Looks like Dave F's response may have hit the nail on the head--I'm using
'03 SP2, and all versions prior to '07 only support 7 nested functions. Of
course, I would have expected an error similar to the "''IF' formula in
excess of 30 arguments" one gets when you exceed that value for one IF if
that was the case, but perhaps not.

But just to be prudent, the purpose of the related column is to "code" the
text in the adjacent cells that describe a certain service contract-type and
term-length. Because the service column's source is a free-form text field
with no validation, there's one correct way to do it (IE: "Premium 1 yr",
"Basic 1 yr"), but two common typo'ed values that correlate to each service
value (IE: "Premium1 yr", "Premium 1yr", "Basic1 yr", "Basic 1yr")... so to
capture all three, I've used an OR method within the nested IF's.

Thanks,

Jamie

"Pete_UK" wrote:

Before I spend a load of time scanning through it, can you explain in
words what it is trying to do and in what way it is failing - error?,
unexpected results? etc

Pete

On Sep 20, 8:26 pm, MJW wrote:
Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)="yr", if(or(left(p2,7)="ASP1 yr", left(p2,7)="ASP 1yr",
left(p2,8)="ASP 1 yr"), "A1", if(or(left(p2,9)="Basic1 yr", left(p2,9)="Basic
1yr", left(p2,10)="Basic 1 yr"), "CC1", if(or(left(p2,11)="Premium1 yr",
left(p2,11)="Premium 1yr", left(p2,12)="Premium 1 yr"), "CC+1",
if(or(left(p2,7)="ASP3 yr", left(p2,7)="ASP3 yr", left(p2,8)="ASP 3 yr"),
"A3", if(or(left(p2,9)="Basic 3yr", left(p2,9)="Basic3 yr",
left(p2,10)="Basic 3 yr"), "CC3", if(or(left(p2,11)="Premium 3yr",
left(p2,11)="Premium3 yr", left(p2,12)="Premium 3 yr"), "CC+3",
if(or(left(p2,7)="ASP5 yr", left(p2,7)="ASP 5yr", left(p2,8)="ASP 5 yr"),
"A5", if(or(left(p2,9)="Basic 5yr", left(p2,9)="Basic5 yr",
left(p2,10)="Basic 5 yr"), "CC5", if(or(left(p2,11)="Premium5 yr",
left(p2,11)="Premium 5yr", left(p2,12)="Premium 5 yr"), "CC+5"))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Hi Duke,

Oh, without a doubt. I'm just not familiar with the method. Please glance
over the response I just sent to Pete in the same thread for the intent,
though I do have to add one caveat explaining the initial
"if(right(p2,2)="yr"" part--this helps to identify new service contracts
versus renewals--new contracts end in the term length abbreviation (yr),
whereas renewals end in, well, "renewal". Appreciate any help you could
provide.

6.02x10^23 thanks!

Jamie



"Duke Carey" wrote:

Without a doubt there's an easier way of handling this than what you've
tried. Rather than forcing us to try to decipher this monster, how about a
simple explanation of your objective and we'll try to get you started down
the right path

"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

I just posted a response to your previous post.

http://www.microsoft.com/office/comm...xp=&sloc=en-us

HTH,
Elkar


"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Elkar,

Thanks! Is there a way to append values such as "A" for any ref cell with
"ASP", "CC" for any ref cell with "Basic" or "CC+" for any ref cell with
"Premium"?

Thanks Again,

Jamie

"Elkar" wrote:

I just posted a response to your previous post.

http://www.microsoft.com/office/comm...xp=&sloc=en-us

HTH,
Elkar


"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Perhaps you could use

=SUBSTITUTE(P2," ","") to eliminate ALL spaces from P2,
Then, check the no spaces version for "Basic"/"Premium" using the SEARCH()
or FIND() functions. That way you needn't check separately for

Basic 1 yr
Basic 1yr
Basic1 yr
Basic1yr

Similarly, you cas use SEARCH()/FIND() to look for "yr"


"MJW" wrote:

Hi Duke,

Oh, without a doubt. I'm just not familiar with the method. Please glance
over the response I just sent to Pete in the same thread for the intent,
though I do have to add one caveat explaining the initial
"if(right(p2,2)="yr"" part--this helps to identify new service contracts
versus renewals--new contracts end in the term length abbreviation (yr),
whereas renewals end in, well, "renewal". Appreciate any help you could
provide.

6.02x10^23 thanks!

Jamie



"Duke Carey" wrote:

Without a doubt there's an easier way of handling this than what you've
tried. Rather than forcing us to try to decipher this monster, how about a
simple explanation of your objective and we'll try to get you started down
the right path

"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Sure, if I understand correctly, you could use:

=IF(RIGHT(A1,2)="yr",LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),"")&IF (NOT(ISERROR(SEARCH("ASP",A1))),"A","")&IF(NOT(ISE RROR(SEARCH("Basic",A1))),"CC","")&IF(NOT(ISERROR( SEARCH("Premium",A1))),"CC+","")

HTH,
Elkar


"MJW" wrote:

Elkar,

Thanks! Is there a way to append values such as "A" for any ref cell with
"ASP", "CC" for any ref cell with "Basic" or "CC+" for any ref cell with
"Premium"?

Thanks Again,

Jamie

"Elkar" wrote:

I just posted a response to your previous post.

http://www.microsoft.com/office/comm...xp=&sloc=en-us

HTH,
Elkar


"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Thanks! Works like a charm.

Much Appreciated,

Jamie

"Elkar" wrote:

Sure, if I understand correctly, you could use:

=IF(RIGHT(A1,2)="yr",LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),"")&IF (NOT(ISERROR(SEARCH("ASP",A1))),"A","")&IF(NOT(ISE RROR(SEARCH("Basic",A1))),"CC","")&IF(NOT(ISERROR( SEARCH("Premium",A1))),"CC+","")

HTH,
Elkar


"MJW" wrote:

Elkar,

Thanks! Is there a way to append values such as "A" for any ref cell with
"ASP", "CC" for any ref cell with "Basic" or "CC+" for any ref cell with
"Premium"?

Thanks Again,

Jamie

"Elkar" wrote:

I just posted a response to your previous post.

http://www.microsoft.com/office/comm...xp=&sloc=en-us

HTH,
Elkar


"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€�yr€�, if(or(left(p2,7)=€�ASP1 yr€�, left(p2,7)=€�ASP 1yr€�,
left(p2,8)=€�ASP 1 yr€�), €œA1€�, if(or(left(p2,9)=€�Basic1 yr€�, left(p2,9)=€�Basic
1yr€�, left(p2,10)=€�Basic 1 yr€�), €œCC1€�, if(or(left(p2,11)=€�Premium1 yr€�,
left(p2,11)=€�Premium 1yr€�, left(p2,12)=€�Premium 1 yr€�), €œCC+1€�,
if(or(left(p2,7)=€�ASP3 yr€�, left(p2,7)=€�ASP3 yr€�, left(p2,8)=€�ASP 3 yr€�),
€œA3€�, if(or(left(p2,9)=€�Basic 3yr€�, left(p2,9)=€�Basic3 yr€�,
left(p2,10)=€�Basic 3 yr€�), €œCC3€�, if(or(left(p2,11)=€�Premium 3yr€�,
left(p2,11)=€�Premium3 yr€�, left(p2,12)=€�Premium 3 yr€�), €œCC+3€�,
if(or(left(p2,7)=€�ASP5 yr€�, left(p2,7)=€�ASP 5yr€�, left(p2,8)=€�ASP 5 yr€�),
€œA5€�, if(or(left(p2,9)=€�Basic 5yr€�, left(p2,9)=€�Basic5 yr€�,
left(p2,10)=€�Basic 5 yr€�), €œCC5€�, if(or(left(p2,11)=€�Premium5 yr€�,
left(p2,11)=€�Premium 5yr€�, left(p2,12)=€�Premium 5 yr€�), €œCC+5€�))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie