Jeffrey Mahoney
Well-known Member
- Joined
- May 31, 2015
- Messages
- 3,242
- Office Version
- 365
- Platform
- Windows
I'm sharing the way I solved the issue because I learned a few things along the way.
I wanted to have the end user enter in a social security number without the dashes and format the number using number format (###-##-####). This doesn't work because the first 3 numbers of a SS# can be 001. So, if you enter a whole number with leading zeros, it doesn't work. The forms I create will not always be used by people that can allow macros, so I have to restrict it to not using VBA and changing the numbers into the correct format.
I went back to formatting the cell as text and having the user enter the number with dashes. This is the data validation formula I used to check the SS# validity.
=AND(LEN(MainSS)=11,ISNUMBER(VALUE(SUBSTITUTE(MainSS,"-",""))))
If anybody can offer another way for the user to enter only numbers, please let me know.
Jeff
I wanted to have the end user enter in a social security number without the dashes and format the number using number format (###-##-####). This doesn't work because the first 3 numbers of a SS# can be 001. So, if you enter a whole number with leading zeros, it doesn't work. The forms I create will not always be used by people that can allow macros, so I have to restrict it to not using VBA and changing the numbers into the correct format.
I went back to formatting the cell as text and having the user enter the number with dashes. This is the data validation formula I used to check the SS# validity.
=AND(LEN(MainSS)=11,ISNUMBER(VALUE(SUBSTITUTE(MainSS,"-",""))))
If anybody can offer another way for the user to enter only numbers, please let me know.
Jeff
