Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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, |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
=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, |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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, |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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, |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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, |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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, |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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, |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
if statements depending on multiple VLOOKUP functions | Excel Discussion (Misc queries) | |||
Logic for Placing Multiple field Returns in a Cell | Excel Discussion (Misc queries) | |||
multiple IF statements | Excel Worksheet Functions | |||
Vlookups and multiple returns (again!) | Excel Worksheet Functions |