excel

[Renetta]

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.

 

[JMB]

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.

 

[JLatham]

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.

 

[Renetta]

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.

 

[JMB]

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

 

[Renetta]

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.