Highlight Irregular Data such as Bärbel?

Bärbel

For example.

These are all names, first and last.

I have 40K rows and need to find any data in any cells that is NOT:

A through Z (upper or lower case), a space, a dash or an apostrophe (all allowed in names).

Thanks for any help, this is driving me bonkers.

Something like (not case sensitive).

=SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)," '-abcdefghijklmnopqrstuvwxyz"))

Not sure how long that will take to process with 40k rows.

Sorry, I should have said, the idea is to enter it into the adjacent column.

Valid names should return a number, anything with disallowed characters will give an error, then you could filter to show errors.

It wasn't clear exactly how you wanted to highlight the incorrect entries, my thought was that this would be more useful than say red / green highlights which would still require you to scroll through the entire list.

I don't know how to customize the formula to my needs I guess. I entered in adjacent cell and changed the A2 in the example to point to the cell I am testing and on enter the formula reports an error.

Did you change both A2's?

Formula should return an error for irregular data or a number for proper names.

See attached.

Hey, THANK YOU for helping me with this! :-)

See if this helps, I can use the formula exactly as in your image with no error so guess you're using european regional settings?

The attached file has a slightly different version of the formula in place, so it should automatically 'translate' to match your settings when you open it.

This is epic. EPIC. It's getting all names with special characters.

How am I going to have it pull entries like A - a single character or DC, two single characters, or a BLANK - no data entered in the field.

THANK YOU THANK YOU THANK YOU :-)

Maybe something like

=IF(LEN(B2)<3,"Error",IF(ISERROR(SUMPRODUCT(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)," '-abcdefghijklmnopqrstuvwxyz"))),"Error",""))

Use <3 so set the minimum number of characters in the name field.

If you want to be able to quickly identify those separately from the invalid characters then you could change one of the "Error" messages.

Were you able to identify the differences between the formula that didn't work and the one in the sample file that did?

Brilliant solution - it catches "John A." however. Thoughts?

This is beyond the COD - I really appreciate it!

EDIT - yes the original error was, of course, mine - I'm set to use pipes instead of commas as delimiters because of another system in which we work.

Are you saying that "John A." is coming up as Error, but is in fact allowable?

If that is the case then you would need to add the period to the string of permitted characters.

The string " '-abcdefghijklmnopqrstuvwxyz" contains all of the permitted characters, you can change that as needed.