Data Validation in IF statement

[CHSLax60]

If I have a field for SSNs, can I confirm that it contains exactly 9 characters? I can't use data validation becase the data could be pasted in. Can I count the number of characters in a cell? The goal is to tell the user that he/she input invalid data. Likewise, if they attempt to paste an "undesirable" character (ie., #, &, $, etc.) into a cell. Can I evaluate that as well?

Excel really needs to have a way to force validation on pasted data.

 

[Skybluekid]

An easy solution would be in the cell next where you want paste the data

=IF(LEN(B3)=9,"","Invalid data")

 

[CHSLax60]

Thanks, it's always the simplest things that you overlook. Now does anyone have something simple on identifing invalid characters so I can really feel like the weekend can't get here soon enough.

 

[Robert Mika]

Try (two methods):
Excel 2010

	A	B	C	D	E
1	, dfadsfad#, &, $,	TRUE	TRUE
2	fdadf&	TRUE	TRUE		&
3	$faefa &	TRUE	TRUE		#
4	fasdfadsf	FALSE	FALSE		$

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =SUMPRODUCT(--(COUNTIF(A1,{"*#*","*&*","*$*"})))>0 C1 =SUMPRODUCT(--(ISNUMBER(SEARCH($E$2:$E$4,A1))))>0 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[CHSLax60]

Thank you sirs. These will work.

 

[Robert Mika]

Thank you sirs. These will work.

You are welcome.