I know that in Access I can set up criteria formulae to trap incorrect formats / structures of input data. I would like to do something similar in Excel 2010. Several of my colleagues use session sheets where they enter attending delegates for a given session, but this data arrives via a variety of methods (SMS, email, etc) and the data supplied may be in an incorrect format. Is there any way that such errors can be trapped when the data is copy/pasted into the session spreadsheet?

eg Correct format - John Smith
Incorrect format - Smith, John Mr

or Correct - Agricultural industries


Incorrect - Industries agricultural

The data will always be in a specific column. When the data arrives in the wrong format and a filter is applied the erroneous data will not appear in the filtered list.

Any advice will be hugely appreciated! Thank you.