Sometimes you get too close to the tree to see the forest...
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
|