IF statement with multiple values...

[guy.pembroke]

Hi,

I would like to be able to search a group of cells for particular text,
placing the true of false value in another cell, using this formula;

=IF(ISNUMBER(SEARCH("ACP",A2)),"ACP","XXX")

My reason for this is that I have a large amount of data that I need to
break down. I have a column that is listed similar to below;

OR/ACP
OM/ACT
OR/MTS
O/O

The part before the / is 'system code'
The part after the / is 'module code'

I want to be able to have one additional column for system code and one
additional column for module code, without having to manually go through all
the data and make any amendments to it.

So as a result, (without the correct syntax) with 'S' being the system
Column and 'M' being the module column, what I would like to achieve is;

If cell contains "O/" then S1 = Office but if cell contains "OR/" then S1 =
Office Range but if cell contains "OM/" then S1 = Office Medium

I hope that makes sense.

Obviously repeated for the module column;

If cell contains "/ACP" then M1 = ACP but if cell contains "/ACT" then M1 =
ACT but if cell contains "/O" then M1 = O

I really hope that makes sense!

 

[Bob Phillips]

I am not sure I get all this, but couldn't you just use

=LEFT(A1,FIND("/",A1)-1)

and

=RIGHT(A1,LEN(A1)-FIND("/",A1))


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[daddylonglegs]

If your data in in A2 downwards try this formula in B2 copied down

=HLOOKUP(LEFT(A2,2),{"OR","OM","O/","";"Office Range","Office
Medium","Office",""},2,0)

and in C2 copied down

=REPLACE(A2,1,FIND("/",A2&"/"),"")

 

[daddylonglegs]

Note:a zero crept into my first formula when it should have been a
letter O, should be

=HLOOKUP(LEFT(A1,2),{"OR","OM","O/","";"Office Range","Office
Medium","Office",""},2,0)

 

[Roger Govier]

Hi Guy
with your data in column A, enter in B1
=IF(LEFT(B21,FIND("/",B21)-1)="OR","Office
Range",IF(LEFT(B21,FIND("/",B21)-1)="OM","Office Medium","Office"))
enter in C1
=TRIM(MID(B21,FIND("/",B21)+1,255))
Copy both cells down columns B and C for the extent of your data.

 

[Roger Govier]

Apologies
I was working on row 21 not row 1 so change to
=IF(LEFT(A1,FIND("/",A1)-1)="OR","Office
Range",IF(LEFT(A1,FIND("/",A1)-1)="OM","Office Medium","Office"))
and
=TRIM(MID(A1,FIND("/",A1)+1,255))
respectively if working from row 1

 

[guy.pembroke]

Thanks Bob, This did achieve what I wanted

)

I am not sure I get all this, but couldn't you just use

=LEFT(A1,FIND("/",A1)-1)

and

=RIGHT(A1,LEN(A1)-FIND("/",A1))


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[guy.pembroke]

Thanks Roger, this did achieve what I wanted!

Apologies
I was working on row 21 not row 1 so change to
=IF(LEFT(A1,FIND("/",A1)-1)="OR","Office
Range",IF(LEFT(A1,FIND("/",A1)-1)="OM","Office Medium","Office"))
and
=TRIM(MID(A1,FIND("/",A1)+1,255))
respectively if working from row 1

 

[guy.pembroke]

Thanks, this did achieve what I wanted!!

 

[Arvi Laanemets]

Hi

Obviously your problem is solved now, but for future:
Add a column to right of one with your codes;
Select the range with codes;
From Data menu, select TextToColumns feature. Set / as (Other) delimiter,
and finish. All entries in column are splitted at once.
(When you want to preserve merged codes too, then create a copy of original
column at start)

 

[Bilal Malik]

Hi,
I wish to find a a valuse/text in a cell which contains large amountof text
and then find the corresponding valuse form another column in the same row. I
was wondering if that is possible to do. To exemplify... fromt the cell
containing the following text..

.........inferred from genetic interaction with Ras85D AND inferred from
genetic interaction with ksr <newline> autophagic cell death ; GO:0048102 |
..........

I want to look for the term 'genetic' and wan the formula to retiurn me
values from the corresponding row in another column.



Thanks
Bilal

 

[Bilal Malik]

Hi,
I wish to find a a valuse/text in a cell which contains large amountof text
and then find the corresponding valuse form another column in the same row. I
was wondering if that is possible to do. To exemplify... fromt the cell
containing the following text..

.........inferred from genetic interaction with Ras85D AND inferred from
genetic interaction with ksr <newline> autophagic cell death ; GO:0048102 |
..........

I want to look for the term 'genetic' and wan the formula to retiurn me
values from the corresponding row in another column.



Thanks
Bilal

 

[Ashish Mathur]

Hi,

Try this

=VLOOKUP("*"&C8&"*",C4:E5,3,0) where C8 has genetic and the range is C4:E5

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com