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:
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi, DAS,
WELCOME to the BOARD!!!!!

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

did you use that one or something else?

kind regards,
Erik
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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
Back
Top