hi,

I need to limit what is entered into a specific column associated with
ssn's. Specifically, I do not want someone to enter dashes or add spaces.
Reason is formating purposes assocated with linking information into Access.

Have you Tried Data Validation (Data/Validation - select whole number,
minimum of zero)

You could also format the cell to show dashes with Format/Cells/Special - SSN.

"Renetta" wrote:

hi,

I need to limit what is entered into a specific column associated with
ssn's. Specifically, I do not want someone to enter dashes or add spaces.
Reason is formating purposes assocated with linking information into Access.

An additional advantage of using Data Validation is that you can have the
system popup a message when the cell is chosen that gives instructions "Enter
your SSAN without spaces or dashes".

I was going to suggest setting a Whole Number range between 100000000 and
999999999 since by definition, an SSAN is a 9-digit number:
1st 3 keyed to the state where it was issued
next 2 indicate order of issue in each area
last 4 are supposedly randomly generated.

"JMB" wrote:

Have you Tried Data Validation (Data/Validation - select whole number,
minimum of zero)

You could also format the cell to show dashes with Format/Cells/Special - SSN.

"Renetta" wrote:

hi,

I need to limit what is entered into a specific column associated with
ssn's. Specifically, I do not want someone to enter dashes or add spaces.
Reason is formating purposes assocated with linking information into Access.

Okay, I've done that and it works thanks. Now that I have done this and
linked my spreadsheet to MS Access another question comes up. See, I failed
to tell you that not all account #'s truly are SSN's they may consist of
Alpha and numeric characters. The probem isn't with excel as I set it up as
text in my Validation. But there is a probem with Access. When the info is
carried over (table is linked to Access) the info reads as numeric instead of
text. I get and error if a Alpha character exists and or if the account #
starts with zeros they are droped from the account # detail. I need all
detail to show for reporting purposes. What can I do. I've tried using an
input mask as "000000000", but that didn't work. Any assistance would be
great.

"Renetta" wrote:

hi,

I need to limit what is entered into a specific column associated with
ssn's. Specifically, I do not want someone to enter dashes or add spaces.
Reason is formating purposes assocated with linking information into Access.

I've not used access enough to offer a good solution. Maybe JLatham has a
suggestion.


"Renetta" wrote:

Okay, I've done that and it works thanks. Now that I have done this and
linked my spreadsheet to MS Access another question comes up. See, I failed
to tell you that not all account #'s truly are SSN's they may consist of
Alpha and numeric characters. The probem isn't with excel as I set it up as
text in my Validation. But there is a probem with Access. When the info is
carried over (table is linked to Access) the info reads as numeric instead of
text. I get and error if a Alpha character exists and or if the account #
starts with zeros they are droped from the account # detail. I need all
detail to show for reporting purposes. What can I do. I've tried using an
input mask as "000000000", but that didn't work. Any assistance would be
great.

"Renetta" wrote:

hi,

I need to limit what is entered into a specific column associated with
ssn's. Specifically, I do not want someone to enter dashes or add spaces.
Reason is formating purposes assocated with linking information into Access.

Hi,

I've set the validation rules as you've indicated, but I'm still running
into problems. Spec. when the data is linked to Access where a ssn starts
with a "0" the "0" drops off. How can I get that to show up? I need all 9
digit to show regardless of the number it starts with. Any suggestions!
Please help.

"Renetta" wrote:

hi,

I need to limit what is entered into a specific column associated with
ssn's. Specifically, I do not want someone to enter dashes or add spaces.
Reason is formating purposes assocated with linking information into Access.