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.

A correct format will be like:
Forename, Surname, Title
John, Smith, Mr

To determine, which part in full name belongs to where is possible only, when there are certain rules how this full name is composed, and the number of those rules is limited, as you have to include all of them into every formula.

P.e.
1 When the full name doesn't have comma in it, then 1st part is may be title with possible values Mr/Ms/Mrs;
2.a. When the full name doesn't have comma in it, then 1st part after title is forename, and the rest is surname;
or
2.b. When the full name doesn't have comma in it, then last part is surname, and the rest except title is forename;
3. Otherwise at end of full name may be title. All before comma is surname, and all after comma and space except title is forname;

When these rules are always fulfilled, you can calculate all parts of name. To keep formulas length reasonable, I advice to use helper columns which you can hide later.
Title:
NameWithoutTitle
ForeName:
etc.

PS. You have to check formulas, as I did write them on fly.

Avril, thank you for such a quick response. I shall indeed try out your code; they have also given me an idea of something a long way back in my experience. :-)

Avril,

Before trying the code on the Delegate field I had a go at Conditional formatting another field and then copy / pasting from an emailed spreadsheet.
The formatting did not work but it did when I typed into the cells.
Is there some bizarre format to data in an emailed document?

Ric

Avril, one thing I tried on the text data fields

Still having no joy with this - I feel so dense!

I am trying a simpler approach first on some copy/pasted data;
eg Sales, Finance, Marketing, Services

I want to check for any imported data that contains errors such as typos or incorrect depts. I tried a Conditional Format rule for one of the words and it worked.

However, as soon as I added further rules for the other words the whole thing fell apart.

Can anyone point me in the right direction? I have tried formulae with AND as well as OR but nothing works.

I'm not sure I understand exactly, what you did. And I'm not sure you understood exactly, what I adviced.

My advice was, to calculate a new field based on entry you got from other sources. I.e. you get some names as single text column, and the formulas split this single-column name to components, which you can use in your tables as they are, or you compose a stadardized full name column based on those. The formulas itself were an example, how to split non-standard full name for one hypothetical set of rules. You have to determine, which set of rules can be applied on your data, and to design formulas according them.

As you talk about conditional formatting, it looks like you try to use latter to indicate entries, which need editing. When so, then:
1. when you can design formulas for conditional formatting, showing faulty entries, then you always can design formulas to split entries too;
2. when you can design formulas to split entries, it doesn't always mean, you can design similar formulas for conditional formatting. Conditional Formatting has some limitations;
3. When you use conditional formatting, then you have manually edit all marked entries. When you split original entries and compose standard name, this work is not needed.

About coping data from web into excel - this is a path for disaster. Web resources often don't use conventional character set. P.e. they can use some unicode characters, which look like normal letters, but are coded differently. And databases and spreadsheets don't operate with character images - they operate with character codes. So when you read a character "A" from web, it may be not same as character "A" you typed into cell from keyboard. And for Excel, p.e. two seemingly identical words "And" and "And" may be entirely different (don't check on what I typed here).

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!