Changing Column Format for SSN

[zeldadawg]

I am trying to add the dashes to the Social Security Number for an entire column(over 12000 lines). I have selected Format - Cells - Special and Custom then OK and the colum does not change. Is there a formula I should use to perform this function?

 

[dianemarie]

Format them as Social Security numbers. Highlight the whole column, right-click and choose Format Cells (or go to Format on the main menu and choose Cells). Click on the number tab and choose Special in the Category box. Click on Social Security and choose OK.

 

[zeldadawg]

I have done that. I want 00/000000000 to read 00/000-00-0000 and using the special or custom did not change them.

 

[dianemarie]

OK, how about this? Are you familiar with Text to Columns (under the Data menu)? Use this to break up your column into three different columns. (Use Fixed Width, then place the breaks by clicking where you want them). Then in an empty column beside the now three separate columns, concatenate the three columns, that is, join them together in one column again, and add the hyphen in between. The formula is (you may know this) =A1&"-"&B1&"-"&C1. This formula will bring your data back together with the hyphens now inserted. You can quickly drop the formula all the way down by double-clicking the fill handle. Be sure to then copy the entire column, and do an Edit-Paste Special-Values, so that the column now holds actual values, instead of formulas.