Hi All,

I am sure this is simple but I have the following information :

A1

CISCO SYSTEMS
Cisco
HEWLETT-PACKARD
Hewlett Packard

What I would like the formula to do is search A1 and if it finds CISCO SYSTEMS make this Cisco, the formula at the same time also needs to check for the word HEWLETT-PACKARD and make this Hewlett Packard.

So the end result would be:

B1
Cisco
Cisco
Hewlett Packard
Hewlett Packard

I don't want to use search and replace as I am trying to build a sheet with vlookup's etc and want to keep this in a formula or funcation.

Thanks for your help.