When a cell contains a certain value
 

Hi,

I have a column of cells with different text in them.

I would like to make a formula that splits these up in categories based
on the text they contain.

I therefore need a formula that for all cells containing the word
"Carlsson" somewhere in the cell, returns a certain value, similarly
for all cells containing for instance "IBM", the formula should return
a certain value.
For all the other cells that haven't been specified, it should for
instance return "Other" as value.

I have tried to use an if sentence saying
=IF(LEFT(A1;4)="Carl";"Carlsson";Other)
That one works when the cells starts with "Carlsson", but the other
ones contain the values i am looking for in the middle of the text
strings.

A pivot table is not an option, as the cells all contain different
albeit similar values.

Can anyone help?

Thx. Lars


--
50pingviner
------------------------------------------------------------------------
50pingviner's Profile: http://www.excelforum.com/member.php...o&userid=29298
View this thread: http://www.excelforum.com/showthread...hreadid=497914

When a cell contains a certain value
 

=IF(COUNTIF(A1,"*Carlsson*")0,"Carlsson","Other")


--

Regards,

Peo Sjoblom

"50pingviner"
wrote in message
...

Hi,

I have a column of cells with different text in them.

I would like to make a formula that splits these up in categories based
on the text they contain.

I therefore need a formula that for all cells containing the word
"Carlsson" somewhere in the cell, returns a certain value, similarly
for all cells containing for instance "IBM", the formula should return
a certain value.
For all the other cells that haven't been specified, it should for
instance return "Other" as value.

I have tried to use an if sentence saying
=IF(LEFT(A1;4)="Carl";"Carlsson";Other)
That one works when the cells starts with "Carlsson", but the other
ones contain the values i am looking for in the middle of the text
strings.

A pivot table is not an option, as the cells all contain different
albeit similar values.

Can anyone help?

Thx. Lars


--
50pingviner
------------------------------------------------------------------------
50pingviner's Profile:
http://www.excelforum.com/member.php...o&userid=29298
View this thread: http://www.excelforum.com/showthread...hreadid=497914

When a cell contains a certain value
 

Try:

=IF(SEARCH("Carlsson",A1),"Carlsson","Other")


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497914

When a cell contains a certain value
 

Note that this will return an error if not found, not "Other"
You need to use either isnumber or iserror like in

=IF(ISNUMBER(SEARCH("Carlsson",A1)),"Carlsson","Ot her")

=IF(ISERROR(SEARCH("Carlsson",A1)),"Other","Carlss on")

--

Regards,

Peo Sjoblom



"Vito" wrote in message
...

Try:

=IF(SEARCH("Carlsson",A1),"Carlsson","Other")


--
Vito
------------------------------------------------------------------------
Vito's Profile:
http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497914

When a cell contains a certain value
 

Peo Sjoblom Wrote:
Note that this will return an error if not found, not "Other"
You need to use either isnumber or iserror like in

=IF(ISNUMBER(SEARCH("Carlsson",A1)),"Carlsson","Ot her")

=IF(ISERROR(SEARCH("Carlsson",A1)),"Other","Carlss on")

--

Regards,

Peo Sjoblom






Thanks. Didn't catch that.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497914

When a cell contains a certain value
 

Thx guys mighty nice of you...:)


--
50pingviner
------------------------------------------------------------------------
50pingviner's Profile: http://www.excelforum.com/member.php...o&userid=29298
View this thread: http://www.excelforum.com/showthread...hreadid=497914