When a cell contains a certain value

[50pingviner]

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

 

[Peo Sjoblom]

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


--

Regards,

Peo Sjoblom

"50pingviner" <[email protected]>
wrote in message
news:[email protected]...

 

[Vito]

Try:

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

 

[Peo Sjoblom]

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","Other")

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

 

[Vito]

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","Other")

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

--

Regards,

Peo Sjoblom

Thanks. Didn't catch that

 

[50pingviner]

Thx guys mighty nice of you...