Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default if statements with multiple returns

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default if statements with multiple returns

=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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default if statements with multiple returns

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default if statements with multiple returns

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default if statements with multiple returns

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 740
Default if statements with multiple returns

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default if statements with multiple returns

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default if statements with multiple returns

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display multiple lines of text within a cell from multiple column. Zeeshan Zaheer Excel Worksheet Functions 3 August 23rd 06 10:08 AM
if statements depending on multiple VLOOKUP functions njuneardave Excel Discussion (Misc queries) 1 June 21st 06 04:33 PM
Logic for Placing Multiple field Returns in a Cell SCHNYDES Excel Discussion (Misc queries) 4 October 22nd 05 12:10 AM
multiple IF statements Nathan McElmurry Excel Worksheet Functions 1 November 18th 04 09:22 PM
Vlookups and multiple returns (again!) Domenic Excel Worksheet Functions 0 November 4th 04 02:49 PM


All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"