Can I do the following in Excel...???

[EIMS]

Hi all,

I have a column of company names (B1:B40000) and want to identify in a seperate column (A) which company names include the word "Trust".

I was thinking of a formula much like the following:

=IF(B1="Trust","Public Sector","")

However, this only works where the cell value is exactly "Trust". Is there a way of ammending this so that if the cell value is "National Trust" it is still recognised?

Perhaps something like this?

=IF(B1="*Trust*","Public Sector","")


Thanks everyone!!!

 

[Krishnakumar]

=IF(ISNUMBER(SEARCH("TRUST",B1)),"Public Sector","")

 

[Darren Bartrup]

Have a look at the Search function (not case sensitive) or Find function (case sensitive).

=SEARCH("Trust","National Trust")
=FIND("Trust","National Trust")

 

[EIMS]

=IF(ISNUMBER(SEARCH("TRUST",B1)),"Public Sector","")

Absolute legend! Thanks Kris!

 

[EIMS]

Oooh... as that was clearly too easy for you... how about ammending the formula so that it searches for multiple values...

=IF(ISNUMBER(SEARCH("TRUST" or "NHS" or "College",B2)),"Public Sector","")

You get the idea! Is this possible?

 

[Krishnakumar]

Hi,

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"trust","nhs","college"},B2))),"Public Sector","")

HTH

 

[EIMS]

Amazing!

Thanks Kris!