How to test for cells that contain a specific character set?

[phaustin]

Hello all!

I am trying to identify a list of last names and first names that contain any characters other than a uppercase letter, space or hyphen.

I've been reading the boards, and I see how to test for the existence of a *specific* character or string, but I want to test for characters that are not letters, spaces, or hyphens.

(As I sidenote, I'd like to also test for any cells that begin or end with a space, but I can probably figure that out.)

Thanks!
Pete

 

[T. Valko]

Here's another one...

Create ths named expression...

Name: Characters
Refers to: ="ABCDEFGHIJKLM- NOPQRSTUVWXYZ"

That space is intentional.

Then:

Book1
	A	B
2	ASDf	FALSE
3	100	FALSE
4	xxx	FALSE
5	Z-A	TRUE
6	A P	TRUE
7	-	TRUE
8	TOM JONES	TRUE
9	J. Theis	FALSE
Sheet1

This array formula** entered in B2 and copied down:

=COUNT(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),Characters))=LEN(A2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[BenMiller]

Brilliant!! I shall worship you; you are clearly a god.

You can always count on T. Valko to come up with something better - and simpler - than the solution you were getting ****y about

 

[T. Valko]

Brilliant!! I shall worship you; you are clearly a god.

You can always count on T. Valko to come up with something better - and simpler - than the solution you were getting ****y about

Sometimes I get it right and sometimes I don't!

 

[phaustin]

I have it working, thank you all. I really appreciate it. You guys really really know what you are doing. Very awesome.

Of course, now I am about to post another question.....

 

[BenMiller]

Post a link to it here

 

[T. Valko]

I have it working, thank you all. I really appreciate it. You guys really really know what you are doing. Very awesome.

Of course, now I am about to post another question.....

You're welcome. We appreciate the feedback!