When a cell contains a certain value

  • Thread starter Thread starter 50pingviner
  • Start date Start date
5

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
 
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")
 
Peo said:
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
Click to expand...


Thanks. Didn't catch that
 
Back
Top