if statements with multiple returns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Batman - 22 Nov 2006 19:21 GMT

Hi someone,

I'm trying to find and return specific text (the 14th and 15th character)
from a cell, but only if its one of 6 different values (ex. "ip" or "iv")

can someone provide a formula that could return only these 2 values, and i
should be ok to replicate it from there??

Thanks,

Reply to this Message

Dave F - 22 Nov 2006 19:25 GMT

=IF(LEFT(A1,14)="i",CONCATENATE(LEFT(A1,14),LEFT(A1,15),"")

Dave

Signature

Brevity is the soul of wit.

> Hi someone,
>
[quoted text clipped - 5 lines]
>
> Thanks,

Reply to this Message

Batman - 22 Nov 2006 19:43 GMT

Hi Dave,

Didn't work for me... how about this way..this returns ip..

=IF(ISNUMBER(SEARCH("IP",F3)),"IP","")

I just can't figure out how to enter multiple values to get mulitple returns.

Ryan

> =IF(LEFT(A1,14)="i",CONCATENATE(LEFT(A1,14),LEFT(A1,15),"")
>
[quoted text clipped - 9 lines]
> >
> > Thanks,

Reply to this Message

Pete_UK - 22 Nov 2006 23:55 GMT

Your data seems to be in F3, so try this:

=IF(OR(MID(F3,14,2)="IP",MID(F3,14,2)="IV"),MID(F3,14,2),"not present")

You can change the "not present" message to something more to your
liking, then copy the formula down if you have similar strings to test
below F3.

Hope this helps.

Pete

> Hi Dave,
>
[quoted text clipped - 21 lines]
> > >
> > > Thanks,

Reply to this Message

Biff - 23 Nov 2006 00:35 GMT

Another way to write that:

=IF(OR(MID(F3,14,2)={"IP","IV"}),MID(F3,14,2),"not present")

Biff

> Your data seems to be in F3, so try this:
>
[quoted text clipped - 37 lines]
>> > >
>> > > Thanks,

Reply to this Message

driller - 22 Nov 2006 20:24 GMT

i think i am confused but i will guess this way..
lets say your lookup table is in i1:i6 (e.g. sorted like ia,ib,ie,ii,jo,yz)
and the text is on cell L9 : BATMAN_AND_ROIBN
14th and 15th is "IB"
the formula guess is
=IF(ISERROR(MATCH(MID(L9,14,2),I1:I6,0)),"no match",MID(L9,14,2))

regards to robin

> Hi someone,
>
[quoted text clipped - 5 lines]
>
> Thanks,

Reply to this Message

Ron Coderre - 22 Nov 2006 20:44 GMT

Try something like this:

With
A1: (the text string to test)

This formula returns the 14th and 15th characters from the string, but only
if they match either "IV" or "IP"
B1: =IF(SUM(COUNTIF(A1,REPT("?",13)&{"IV","IP"}&"*")),MID(A1,14,2),"missing")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

> Hi someone,
>
[quoted text clipped - 5 lines]
>
> Thanks,

Reply to this Message

Aladin Akyurek - 26 Nov 2006 00:41 GMT

Assuming that the string of interest is in A2 and the list of specific
text bits are (adjust to suit): "ip","iv","dp","dv","fi",and "xi"...

Try in B2:

=LOOKUP(9.99999999999999E+307,SEARCH({"Not
Found","ip","iv","dp","dv","fi","xi"},"Not Found"&MID(A2,14,2)),
{"Not Found","ip","iv","dp","dv","fi","xi"})

Note "Not Found" which is added to the preset list of bits of text.

> Hi someone,
>
[quoted text clipped - 5 lines]
>
> Thanks,

Reply to this Message