Social Security Formating

DAS

New Member
Joined
Jul 11, 2008
Messages
36
I tried to format 9 digits to display from XXXXXXXXX to XXX-XX-XXXX.
I tried doing so by formating the cell to special then to selecting the social format but it won't work on my excel. It worked on other computers but not mine. How can I fix this?

Thanks:confused:
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, DAS,
WELCOME to the BOARD!!!!!

this should work
###-##-####

did you use that one or something else?

kind regards,
Erik
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
maybe the cells have been previously formatted as text or they have some non-printing characters. If you double-click in the cell and put your cursor before the first digit in your number, hit the backspace key and then Enter. Then you should be able to format the cell with the SS format. If you have to do this to a lot of cells you can use the macro below to clean them up.

Code:
Sub CleanCells2()
Dim cell As Range
    
    intResponse = MsgBox("This macro will remove all non-printing characters" & vbCrLf & "and excess spaces from all selected cells. Continue?", vbExclamation + vbOKCancel, "Cell Cleaner")
    If intResponse = vbOK Then
        intResponse = MsgBox("Do you want to clear the formatting as well?", vbQuestion + vbYesNo)
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        If intResponse = vbYes Then Selection.ClearFormats
        On Error Resume Next
        For Each cell In Selection.SpecialCells(xlCellTypeConstants)
            cell.Value = Application.WorksheetFunction.Clean(cell.Value)
            cell.Value = Application.WorksheetFunction.Trim(cell.Value)
        Next cell
        Err.Clear
        Set cell = Nothing
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
        MsgBox "The Selected Cells Have Been Cleaned!", vbExclamation + vbOKOnly
    On Error GoTo 0
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,384
Messages
5,601,323
Members
414,442
Latest member
Oleg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top