Customize Cell

kcgojnur

Board Regular
Joined
Aug 13, 2014
Messages
122
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
You could use Data Validation>Custom with this formula:
Code:
=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.
 
Upvote 0
You could use Data Validation>Custom with this formula:
Code:
=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?
 
Upvote 0
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?
 
Upvote 0
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
Code:
=AND(LEN(F3)=9,ISNUMBER(F3))

I did 123456789, and I get the error message. Any idea as to what can cause this?
 
Upvote 0
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
 
Last edited:
Upvote 0
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)
 
Last edited:
Upvote 0
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:
Code:
=AND(LEN(F3)=9,LEN(SUBSTITUTE(F3,CHAR(ROW(48:57)),""))=0)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top