Customize Cell

[kcgojnur]

Hello -

Is there a way to customize a column of cells that requires 9 digits (no more or less) and no dashes? This column will be used to enter social security numbers. I was able to customize no dashes, however, not sure how to require 9 digits in addition to no dashes.

 

[JoeMo]

You could use Data Validation>Custom with this formula:

=AND(LEN(A1)=9,ISNUMBER(A1))

to allow only 9-digit numbers.
However, this will not prevent some invalid social security numbers (SSN's) which take the form Area-Group-Serial where Area is 3 digits, group is 2 digits and serial is 4 digits. Valid SSN's cannot be all zeroes in any of the three parts (e.g. 000-15-12345 or 123-00-12345 or 123-45-0000).
The custom formula will screen out any 9-digit number starting with a zero, but will allow all zeroes in either the group or serial positions. Further, it will not screen Area numbers like 666 which have never been issued.

I suspect you could get something more suited to SSN's by searching the internet or perhaps just running a search on this forum.

 

[kcgojnur]

You could use Data Validation>Custom with this formula:

=AND(LEN(A1)=9,ISNUMBER(A1))

to allow only 9-digit numbers.
However, this will not prevent some invalid social security numbers (SSN's) which take the form Area-Group-Serial where Area is 3 digits, group is 2 digits and serial is 4 digits. Valid SSN's cannot be all zeroes in any of the three parts (e.g. 000-15-12345 or 123-00-12345 or 123-45-0000).
The custom formula will screen out any 9-digit number starting with a zero, but will allow all zeroes in either the group or serial positions. Further, it will not screen Area numbers like 666 which have never been issued.

I suspect you could get something more suited to SSN's by searching the internet or perhaps just running a search on this forum.

Hi Joe - I tried the formula provided however it doesn't seem to work. I entered 123456789 and I got the pop up indicating error. Any suggestions?

 

[JoeMo]

Hi Joe - I tried the formula provided however it doesn't seem to work. I entered 123456789 and I got the pop up indicating error. Any suggestions?

Works fine for me with 123456789. I can only assume you didn't apply the formula correctly. You are using it in Data Validation, yes?

 

[kcgojnur]

Works fine for me with 123456789. I can only assume you didn't apply the formula correctly. You are using it in Data Validation, yes?

I used the following in data validation

=AND(LEN(F3)=9,ISNUMBER(F3))

I did 123456789, and I get the error message. Any idea as to what can cause this?

 

[Peter_SSs]

Any idea as to what can cause this?

My guess is that F3 is formatted as Text (possibly to preserve any leading zeros). Try this instead
=AND(LEN(F3)=9,ISNUMBER(F3+0))

Note though that this type of DV would allow an entry like 1234567E9 or 12JUN2345

 

[Peter_SSs]

Note though that this type of DV would allow an entry like 1234567E9 or 12JUN2345

To disallow entries like that, try this as your custom DV formula (this will also still disallow dashes)

=AND(LEN(F3)=9,SUM(9-LEN(SUBSTITUTE(F3, CHAR(ROW(INDIRECT("48:57"))), "")))= 9)

 

[kcgojnur]

To disallow entries like that, try this as your custom DV formula (this will also still disallow dashes)

=AND(LEN(F3)=9,SUM(9-LEN(SUBSTITUTE(F3, CHAR(ROW(INDIRECT("48:57"))), "")))= 9)

Thanks Peter, worked perfectly!

 

[JoeMo]

To disallow entries like that, try this as your custom DV formula (this will also still disallow dashes)

=AND(LEN(F3)=9,SUM(9-LEN(SUBSTITUTE(F3, CHAR(ROW(INDIRECT("48:57"))), "")))= 9)

Can that be shortened to:

=AND(LEN(F3)=9,LEN(SUBSTITUTE(F3,CHAR(ROW(48:57)),""))=0)

 

[Peter_SSs]

Can that be shortened to:

=AND(LEN(F3)=9,LEN(SUBSTITUTE(F3,CHAR(ROW(48:57)),""))=0)

Have you tried it?