Find Social Security Number in a String of Text

[SoldOnTheRoad]

Good morning. I have searched and found several variations but no solutions.

I am performing an audit to find cells that contain a social security number (SSN). I have tried a COUNTIF formula with "???-??-????" and find/replace functions. The problem is these result in false positives, typically flagging dates. I'm looking to extract any 9 digits, either in the form of "###-##-####", "###/##/####", or "#########". I need to keep any leading zeros. Any help is appreciated. Happy Good Friday/Easter everyone.

The cow jumped over the moon

The cow 123-45-6789 over the moon

The cow 123456789 over the moon

The cow 1/1/21 over the moon

The cow 123/45/6789 over the moon

The cow 01-01-2001 over the moon

On 1/1/2001. The cow jumped over the moon

On 1/1/2001. The cow 123-45-6789 over the moon

Customer 9900990099009900. The cow 123456789 over the moon

On 1/1/2021, the cow 1/1/21 over the moon

9-9-21. The cow 123/45/6789 over the moon

9/9/21. The cow 01-01-2001 over the moon

 

[JonXL]

The only thing that comes to mind is using a UDF (user defined VBA function). But perhaps that's 'cause that's the only hammer I generally have(?)

Writing it would be easy enough, but before I jump into that, can I ask you to take the table you provided and add in an expected outcome? Which of these should be identified as having an SSN? How would that identification look (a TRUE/FALSE, a list of cell addresses concatenated in one cell, something else)? And how many cells do you have to check?

 

[SoldOnTheRoad]

The only thing that comes to mind is using a UDF (user defined VBA function). But perhaps that's 'cause that's the only hammer I generally have(?)

Writing it would be easy enough, but before I jump into that, can I ask you to take the table you provided and add in an expected outcome? Which of these should be identified as having an SSN? How would that identification look (a TRUE/FALSE, a list of cell addresses concatenated in one cell, something else)? And how many cells do you have to check?

Thank you for the response. I'd love some help with VBA.

Below is the expected outcome in column B (Y/N) - there will still need to be a manual audit. We are looking across 20 years of data; approximately 400,000 records.

String	Expected Result	Note
The cow jumped over the moon	N	No numbers
The cow 023-45-6789 over the moon	Y	Yes - finds SSN in ###-##-#### format
The cow 123456789 over the moon	Y	Yes - finds SSN in ######### format
The cow 1/1/21 over the moon	N	No - doesn't meet length or format
The cow 123/45/6789 over the moon	Y	Yes - finds SSN in ###/##/#### format
The cow 01-01-2001 over the moon	N	No - doesn't meet length or format
On 1/1/2001. The cow jumped over the moon	N	No - doesn't meet length or format
On 1/1/2001. The cow 123-45-6789 over the moon	Y	Yes - finds SSN in ###-##-#### format; ignores date
Customer 9900990099009900. The cow 123456789 over the moon	Y	Yes - finds SSN in ######### format; ignores string >9 numbers
On 1/1/2021, the cow 1/1/21 over the moon	N	No - doesn't meet length or format
9-9-21. The cow 123/45/6789 over the moon	Y	Yes - finds SSN in ###/##/#### format; ignores date
9/9/21. The cow 01-01-2001 over the moon	N	No - doesn't meet length or format

 

[JonXL]

Okay. It is handy that VBA has a LIKE operator because, in my opinion, that is what you really need here to make this easy. So all our UDF does is open that VBA function up to you to use in a formula w/in the cells. Here is the function I wrote:

Function IsLike(stringValue As String, likePattern As String) As Boolean

    IsLike = stringValue Like likePattern

End Function

That just needs to go in a module in your workbook.

Now to use it, as in your particular case, I put this formula into cell D2 and copied it down (with your original values starting in A1):

=IF(OR(IsLike(A2,"*[!0-9]###[-/]##[-/]####[!0-9]*"),IsLike(A2,"*[!0-9]#########[!0-9]*")),"Y","N")

Here is the output (note, I split your ninth example into two to properly test the condition of more than nine numbers in a row):

Book1
	A	B	C	D
1	String	Expected Result	Note	UDF
2	The cow jumped over the moon	N	No numbers	N
3	The cow 023-45-6789 over the moon	Y	Yes - finds SSN in ###-##-#### format	Y
4	The cow 123456789 over the moon	Y	Yes - finds SSN in ######### format	Y
5	The cow 1/1/21 over the moon	N	No - doesn't meet length or format	N
6	The cow 123/45/6789 over the moon	Y	Yes - finds SSN in ###/##/#### format	Y
7	The cow 01-01-2001 over the moon	N	No - doesn't meet length or format	N
8	On 1/1/2001. The cow jumped over the moon	N	No - doesn't meet length or format	N
9	On 1/1/2001. The cow 123-45-6789 over the moon	Y	Yes - finds SSN in ###-##-#### format; ignores date	Y
10	The cow 123456789 over the moon	Y	Yes - finds SSN in ######### format	Y
11	On 1/1/2021, the cow 1/1/21 over the moon	N	No - doesn't meet length or format	N
12	9-9-21. The cow 123/45/6789 over the moon	Y	Yes - finds SSN in ###/##/#### format; ignores date	Y
13	9/9/21. The cow 01-01-2001 over the moon	N	No - doesn't meet length or format	N
14	Customer 9900990099009900. The cow over the moon	N	No - ignores string >9 numbers	N
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=IF(OR(islike(A2,"*[!0-9]###[-/]##[-/]####[!0-9]*"),islike(A2,"*[!0-9]#########[!0-9]*")),"Y","N")

 

[Snakehips]

FWIW My take was this....

Function SSN(Str As String) As String
Dim Arry As Variant
Dim i As Integer
SSN = "N"
Str = Replace(Replace(Str, "-", "", 1), "/", "", 1)
Arry = Split(Str, " ")

For i = LBound(Arry) To UBound(Arry)
    If (Len(Arry(i)) = 9 And IsNumeric(Arry(i))) Then
        SSN = "Y"
        Exit For
    End If
Next
End Function

Book1
	A	B
1
2	The cow jumped over the moon	N
3	The cow 123-45-6789 over the moon	Y
4	The cow 123456789 over the moon	Y
5	The cow 1/1/21 over the moon	N
6	The cow 123/45/6789 over the moon	Y
7	The cow 01-01-2001 over the moon	N
8	On 1/1/2001. The cow jumped over the moon	N
9	On 1/1/2001. The cow 123-45-6789 over the moon	Y
10	Customer 9900990099009900. The cow 123456789 over the moon	Y
11	On 1/1/2021, the cow 1/1/21 over the moon	N
12	9-9-21. The cow 123/45/6789 over the moon	Y
13	9/9/21. The cow 01-01-2001 over the moon	N
Sheet9

Cell Formulas
Range	Formula
B2:B13	B2	=SSN(A2)

 

[JonXL]

@Snakehips, will that catch "my ssn is 123-45-6789."? (Note the period at the end.)

 

[Snakehips]

@Snakehips, will that catch "my ssn is 123-45-6789."? (Note the period at the end.)

@JonXL No it won't! Well spotted.

 

[Rick Rothstein]

I think this function will work correctly...

Function HasSSN(S As String) As Boolean
  HasSSN = " " & S & " " Like "*###[/-]##[/-]####*" Or " " & S & " " Like "*[!A-Za-z0-9]#########[!A-Za-z0-9]*"
End Function

Edit Note: My originally posted code had a small flaw in it so I modified my code about 10 minutes after originally posting it.

 

[Snakehips]

I think this function will work correctly...

Function HasSSN(S As String) As Boolean
  HasSSN = " " & S & " " Like "*###[/-]##[/-]####*" Or " " & S & " " Like "*[!A-Za-z0-9]#########[!A-Za-z0-9]*"
End Function

It returns an incorrect result for, eg "Customer 990-09-90099009900. The cow over the moon"
I think it fair to say that @JonXL has provided the most effective solution.

 

[Rick Rothstein]

I believe this fixes my function so that it will work correctly...

Function HasSSN(S As String) As Boolean
  HasSSN = " " & S & " " Like "*[!0-9]###[/-]##[/-]####[!0-9]*" Or " " & S & " " Like "*[!0-9]#########[!0-9]*"
End Function