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!

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" wrote in message
...
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!

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
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=545020

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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=545020

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.
--
Regards

Roger Govier


"guy.pembroke" wrote in message
...
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!

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

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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.
--
Regards

Roger Govier


"guy.pembroke" wrote in
message ...
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!

Thanks Bob, This did achieve what I wanted

:o)

"Bob Phillips" wrote:


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" wrote in message
...
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!

Thanks, this did achieve what I wanted!!

"daddylonglegs" wrote:


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
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=545020

Thanks Roger, this did achieve what I wanted!

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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.
--
Regards

Roger Govier


"guy.pembroke" wrote in
message ...
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!

Hi

Obviously your problem is solved now, but for futu
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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"guy.pembroke" wrote in message
...
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!

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


"guy.pembroke" wrote:

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!

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


"guy.pembroke" wrote:

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!

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

"Bilal Malik" wrote in message
...
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


"guy.pembroke" wrote:

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!