Formatting cells for social security #'s

[kcgojnur]

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

=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

 

[shg]

How about just whole number between 0 and 999999999?

 

[kcgojnur]

That would work, just allows any sequence of 9 numbers but no dashes or less then 9

 

[shg]

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.

 

[kcgojnur]

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?

 

[shg]

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.

 

[kcgojnur]

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.

 

[shg]

Format the relevant column as Text, and put this in the sheet module:

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

 

[kcgojnur]

Format the relevant column as Text, and put this in the sheet module:

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.

 

[Fluff]

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.