Social Security Formatting Excel Cells 1

[RAxel]

I receive files that have social securities for people and they're saved as type general and don't have the dashes written. The problem with this are social securities who have leading 0's. So, a social like 001-11-1111, is saved as 1111111.

How can I format the cells so that there are nine numbers displayed? If it's possible, putting in dashes (not just having them being formatted with dashes) would be great too. Thanks.

 

[AnotherHiggins]

What application?

If Excel, just change the format of the cell.

Go to Format > Cells then on the Number tab choose Special in the list on the left and Social Security Number from the list on the right.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

Q: When a numbers not NUMBERS?

A: When they are not used arithmetically. And sometimes when FORMAT is essential.

So Part Number, Invoice Numbers, Social Security Numbers.....

are NOT REALLY NUMBERS. Rather, they are TEXT STRINGS of numeric digits, usually with some consistent format.

The FORMAT that John suggests will work with leading zeros if you pre-format the cells that you will be using.

If you want to actually enter the dashes, that will work also.

However, you must be consistent. Enter an identical example of a each method and look at the DIFFERENCE in the values in the Formula Bar.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[RAxel]

The thing is, I have to upload the excel file into Access and I want the leading 0's to be displayed in Access.

Even though anotherhiggins is correct that the social will be displayed correctly in excel with his method, it isn't really typed like that; so a social like 111111 in Excel, formatted with the social security format to look like 001-11-1111 is uploaded as 1111111 and I'd have to go into Access and manually add the two leading 0's.

I want the socials to be a text string with all leading 0's. How can I go about doing that? Thanks.

 

[SkipVought]

Did you read my post, "Q: When a numbers not NUMBERS?"

It answers this question.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[RAxel]

I'm sorry but I do not follow it. Are you saying format the cells as 'Text' and then format as 'Social Security'? If that's the case, that doesn't work because converting it to text gets rid of the leading 0's.

Please clarify and you might have to dumb it down even more for me.

P.S. Are you saying by "pre-format", going into every cell and adding the 0's myself?

 

[AnotherHiggins]

If you already have a lot of entries that you need to convert, then you can use
[COLOR=blue white]=text(a2,"000-00-0000")[/color]
to convert the number 1234567 to 001-23-4567.

Change A2 to whatever cell you need.

Copy the formula down as far as needed.


[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[RAxel]

Thanks a bunch anotherhiggins.

Just out of curiousity, can I delete the original column now since I have one of type text? Obviously if I straight up delete it the values would be 000-00-0000 in the text column, is there a way around that? Thanks!

 

[AnotherHiggins]

FYI, it actually wouldn't be 000-00-0000, it would be #REF! because the cell it was referencing no longer exists.

But to answer your question: Yes.

[ul][li]Select the new column (the one with the formula)[/li]
[li]Copy[/li]
[li]Paste Special > Values[/li][/ul]

Now you can delete the first column and still have your data stored as desired.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[RAxel]

Very neat. Thanks again!

 

[AnotherHiggins]

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.