Hello, Avril. My apologies - I did understand your advice on the splitting of the name field, I was going for one of the easier fields first. There are four division fields and I was trying to create a check for typos. I can get a formula [COLOR="Purple"](=IF(AND(J2<>"Automotive/LLP",J2<>"Retail",J2<>"Supply Chain",J2<>"Consumer, EM",J2<>"Life Sciences"),"Error","")[/COLOUR] to work but it means setting up four extra columns to report the errors (one column for each check) and my wife doesn't want this. I tried the formula in the appropriate part of a conditional format but it wouldn't work there.
You have confirmed my suspicion that the data in an email or SMS is different. Perhaps this isn't going to work at all. I have recommended that their system be made into a database but it has been a spreadsheet for so long that they do not feel they can change.
My thanks to you for your advice and suggestions. It has been fun trying to address the problem. Have a wonderful Christmas.

1. You can hide helper columns (and it will be better to place them at end of table).

2. Instead of helper columns you can use dynamical names. You select the row in table, create an new name, and set its source like the formula you used in helper column FOR THIS ROW. Now when you insert into some column for checking a formula = MyDynamicalName, for every row the name must return a value proper for this row. There is a caveat - Conditional Format doesn't understand anything about names. So you must have one helper column anyway, where you calculate a result value based on all names - then you can use the values in this column for your conditional format.

And Merry Christmas for you too!