Formatting cells for social security #'s

kcgojnur

Board Regular
Joined
Aug 13, 2014
Messages
122
Hello -

I'm trying to create a column(F) in which the user would need to enter their 9 digit social security number without dashes. Therefore if they enter a dash, or don't enter the full 9 digits they should be prompted to do so with a pop up. I tried data validation and entered the following
Code:
=AND(LEN(F3)=9,SUM(9-LEN(SUBSTITUTE(F3, CHAR(ROW(INDIRECT("48:57"))), "")))= 9)
this doesn't seem to work b/c if I enter just the 9 digits I still get an error notification.

Any help is greatly appreciated
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
How about just whole number between 0 and 999999999?
 
Upvote 0
It would allow entry of, for example, 1.3E4, but that seems unlikely as a source of error.

To be rigorous in enforcing entry of nine digits, the cell would need to be formatted as Text and backed up with change event code that checks the input and then formats it.
 
Upvote 0
It would allow entry of, for example, 1.3E4, but that seems unlikely as a source of error.

To be rigorous in enforcing entry of nine digits, the cell would need to be formatted as Text and backed up with change event code that checks the input and then formats it.


The cells are already formatted as text. I'm not understanding as to why I'm getting error by entering 9 digits. Any way you can explain what to do based on your suggestion?
 
Upvote 0
The cells are already formatted as text. I'm not understanding as to why I'm getting error by entering 9 digits.
If a cell is formatted as Text, any entry you make is text, not a number, so it fails data validation. In addition, custom number formats (e.g., for SSN format) apply to numbers, not text.

Any way you can explain what to do based on your suggestion?
Yes, if you are comfortable implementing and deploying a VBA-based solution, which would require users to enable macros.
 
Last edited:
Upvote 0
If a cell is formatted as Text, any entry you make is text, not a number, so it fails data validation. In addition, custom number formats (e.g., for SSN format) apply to numbers, not text.


Yes, if you are comfortable implementing and deploying a VBA-based solution, which would require users to enable macros.

Yes, at this point if macro is the way to go I'm open to next steps.
 
Upvote 0
Format the relevant column as Text, and put this in the sheet module:

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rInt          As Range
  Dim cell          As Range

  With Target
    Set rInt = Intersect(Target, Me.UsedRange, Range("A:A"))  ' change column as required
    If Not rInt Is Nothing Then
      On Error GoTo Oops
      Application.EnableEvents = False
      
      For Each cell In rInt.Cells
        If Not IsEmpty(cell.Value) Then
          If cell.Value Like "#########" Then
            cell.Value = Format(cell.Value, "@@@-@@-@@@@")
          
          Else
            cell.Select
            MsgBox "Invalid entry"
            rInt.ClearContents
            Exit For
          End If
        End If
      Next cell
    End If
  End With

Oops:
  Application.EnableEvents = True
End Sub
 
Upvote 0
Format the relevant column as Text, and put this in the sheet module:

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rInt          As Range
  Dim cell          As Range

  With Target
    Set rInt = Intersect(Target, Me.UsedRange, Range("A:A"))  ' change column as required
    If Not rInt Is Nothing Then
      On Error GoTo Oops
      Application.EnableEvents = False
      
      For Each cell In rInt.Cells
        If Not IsEmpty(cell.Value) Then
          If cell.Value Like "#########" Then
            cell.Value = Format(cell.Value, "@@@-@@-@@@@")
          
          Else
            cell.Select
            MsgBox "Invalid entry"
            rInt.ClearContents
            Exit For
          End If
        End If
      Next cell
    End If
  End With

Oops:
  Application.EnableEvents = True
End Sub

Thanks, I'll give it a try and keep you posted.
 
Upvote 0
Cross posted https://www.excelforum.com/excel-general/1246079-formatting-cells-for-social-security-s.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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