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,

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

Dave
--
Brevity is the soul of wit.


"Batman" wrote:

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,

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

"Dave F" wrote:

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

Dave
--
Brevity is the soul of wit.


"Batman" wrote:

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,

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

Batman wrote:

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

"Dave F" wrote:

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

Dave
--
Brevity is the soul of wit.


"Batman" wrote:

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,

Another way to write that:

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

Biff

"Pete_UK" wrote in message
ups.com...
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

Batman wrote:

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

"Dave F" wrote:

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

Dave
--
Brevity is the soul of wit.


"Batman" wrote:

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,

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

"Batman" wrote:

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,

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


"Batman" wrote:

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,

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.


Batman wrote:
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,