Recognizing SSN

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hello All,

I have cells formatted to enter SSNs as numbers. I import
this into MS Access. The problem if the SSN starts with a
zero.

So if someone enters "099-88-7777" the Excel calculates
this as "99887777". It drops the zero. I do not want to
add ' in front the SSN because Access imports the '.

How can I make recognize the zeros? Thanks for any help.
 
Format the cells as text before anyone types anything in.

If you are typing numbers with the text "-" between them, Excel should leave
it exactly as you typed it, anyhow.
 
Hi
you may use a custom format like
000-00-0000

or in a separate column use the formula
=TEXT(A1,"000-00-0000")
 
This does not work if the data is already there, only when entering new information. I have the same problem with SSNs and TAX ID's going back and forth between Access and Excel - When importing I make sure to change the field type to text, this has helped when I am controlling. I doesnt if some one sends me one that has already been imported to excel.

I need a better solution too. The last two entries were not as helpful as I had hoped them to be. Anybody else know about this?

Thanks for trying!
 
Frank's second suggestion *will* work for already existing data.
But you will then have to remove the "repair" formula and leave the revised
data behind.

Select the "repaired" data,
Right click in the selection, and choose "Copy".
Right click again and choose "PasteSpecial".
Click on "Values", then <OK>.

You now have your column of text numbers to do with as you please.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


This does not work if the data is already there, only when entering new
information. I have the same problem with SSNs and TAX ID's going back and
forth between Access and Excel - When importing I make sure to change the
field type to text, this has helped when I am controlling. I doesnt if some
one sends me one that has already been imported to excel.

I need a better solution too. The last two entries were not as helpful as I
had hoped them to be. Anybody else know about this?

Thanks for trying!
 
Back
Top