Validate string entry

Question

I need to validate a Social Security Number (SSN) in Excel. I don't know where to start and I've looked everywhere on the web, but to no avail ... Please help if you can. Thanks.

Here are the validation rules:

• Field length always 9 numeric digits.
• Can not be all same digits (e.g. 222-22-2222)
• Can not contain continuous, sequential numbers from 1 to 9 or 9 to 1 (i.e. 123-45-6789, 9876-54-321)
• Can not contain data other than numerical digits (0-9)
• Restricted numbers beginning with 666, 000.
• Can not contain 9 as the first number.`
• Last four digits can not be zeros (0000).

Answer 1

regexp solution below that draws heavily from here

I note that this condition

Can not contain continuous, sequential numbers from 1 to 9 or 9 to 1 (i.e. 123-45-6789, 9876-54-321)

is already invalidated by:

Can not contain 9 as the first number.

Using the code

1. Press AltF11 to go to the visual basic editor
2. Insert .... Module
3. Copy and Paste in the code starting from Sub OCD_Kid()
4. Press AltF11 to get back to Excel

You can then use this User Defined Formula (UDF) to test values in your spreadsheet directly.

• If A1 had 070-22-2794,
• in B1 enter =OCD_Kid(A1) to test the string in A1

main code

 Function OCD_Kid(strIn As String) As Boolean
 Dim objRegex As Object
 Set objRegex = CreateObject("vbscript.regexp")
 With objRegex
    .Global = True
    .Pattern = "^(?!\b(\d)\1+-(\d)\1+-(\d)\1+\b)(?!123-45-6789)(?!666|000|9\d{2})\d{3}-\d{2}-(?!0{4})\d{4}$"
    OCD_Kid = .test(strIn)
 End With
 End Function

test

 Sub Tested()
 Debug.Print OCD_Kid("222-22-2222") ' Invalid
 Debug.Print OCD_Kid("070-22-2794") ' Valid
 Debug.Print OCD_Kid("823-45-6789") ' Valid
 Debug.Print OCD_Kid("123-45-6789") ' Invalid
 Debug.Print OCD_Kid("123-45-5789") ' Valid
 End Sub

Answer 2

This is not a complete answer.

However, it will give you a template you can expand to meet more criteria. Here is a User Defined Function (UDF)

Public Function SSNCheck(s As String) As String
    Dim i As Long
    SSNCheck = "Bad"
    If Len(s) <> 11 Then Exit Function
    ary = Split(s, "-")
    If UBound(ary) <> 2 Then Exit Function
    For i = 0 To 2
        If Not IsNumeric(ary(i)) Then Exit Function
    Next i
    If Len(ary(0)) <> 3 Then Exit Function
    If Len(ary(1)) <> 2 Then Exit Function
    If Len(ary(2)) <> 4 Then Exit Function
    If ary(2) = "0000" Then Exit Function
    If ary(0) & ary(1) & ary(2) = "123456789" Then Exit Function
    SSNCheck = "Good"
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=SSNCheck(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!