Testing for a value that contains an ampersand

[DerfNotwen]

I'm converting a series of names from simple Joe Bloggs to joe.bloggs@whispers.com. No problem there
However, a couple of the names are odd and I have an If statement that I need to use to check them and do something different
=If(H1="Support",H1&"@whispers.com") - easy enough
The problem comes when the odd name contains an ampersand as the test removes it and concatenates the letters either side
If(H1="BP&D Suppliers Limited","Suppliers.Limited@whispers.com") fails as it tests for BPD Suppliers Limited

How do I test for "BP&D Suppliers Limited" please ?

I've tried imbedding Chr(38) in place of the ampersand, it comes back with ?Name

I'm sure there's a solution, but searching message boards/google hasn't revealed it

Thanks in advance
Fred

 

[steve the fish]

That would suggest H1 doesnt equal BP&D Suppliers Limited not that its testing for something else. What does this produce?

=LEN(H1)=LEN("BP&D Suppliers Limited")

and

=H1="BP&D Suppliers Limited"

 

[DerfNotwen]

That would suggest H1 doesnt equal BP&D Suppliers Limited not that its testing for something else. What does this produce?

=LEN(H1)=LEN("BP&D Suppliers Limited")

and

=H1="BP&D Suppliers Limited"

thanks for the pointer, your hint about "doesn't equal BP&D Suppliers Limited" set me thinking about the cell format. Checking it showed General, I changed it to Text and, lo and behold, it worked

Thanks for the help
Regards
Fred