SSN formatting problem

[LTUser54]

Seeking help with SSN formatting. I imported a file, lost the leading zero in the ssn, fixed it this way:

cell D18 99999999

using formula: =RIGHT("000000000" & D18,9) gave me

099999999, BUT, for the life of me I cannot get the cell to show 099-99-9999

I tried custom format , paste special "value" and format cell special social security number, right clicking & SSN format, looked here for answers, and cannot find a solution.

Is this one of those things Excel cannot do?

Any help is appreciated.

Thanks.

[jasoncw]

I think Excel needs to recognize the cell as a value, rather than text. Using the LEFT/RIGHT/MID functions returns text, so instead of:

=RIGHT("000000000" & D18,9)

Use:

=VALUE(RIGHT("000000000" & D18,9))

HTH

Jason

[LTUser54]

I think Excel needs to recognize the cell as a value, rather than text. Using the LEFT/RIGHT/MID functions returns text, so instead of:

=RIGHT("000000000" & D18,9)

Use:

=VALUE(RIGHT("000000000" & D18,9))

HTH

Jason

Jason,

Dang, that's the trick!! Using value in the formula I can apply SSN format & the dashes show up.

Thanks SO MUCH for posting your reply.

I truly appreciate it.

Mark
aka LTUser
Boston, MA

[jasoncw]

Glad it helped, Mark.

Jason