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

 

[SoldOnTheRoad]

Good morning - First of all, thank you everyone for their help and input; we're getting closer. 2 questions.
1. @Rick Rothstein - what is the Excel formula to execute that VBA function?
2. @JonXL - I'm getting a lot of false positives because the cells may contain a the cells may contain a 9 digit account number preceded by a letter, typically a "L". Can the VBA look to exclude those? My apologies for not initially calling that out in my request.

Thank you again.

 

[Rick Rothstein]

1. @Rick Rothstein - what is the Excel formula to execute that VBA function?

Assuming cell A1 contains your text...

=HasSSN(A1)

 

[SoldOnTheRoad]

Assuming cell A1 contains your text...

=HasSSN(A1)

Well that was easy. Thank you. Waiting for a response as well from JonXL - I failed to mention that the cells may contain an account number like "L123456789" and those are getting picked up. Also, it looks like some dates or phone numbers are being flagged as Y/True; which is not a big deal since we're down to auditing approximately 2k records from 400k. Thank you again.

 

[JonXL]

With mine a change to the formula should suffice to get you different results - no recoding necessary. This should account for the letters at the beginning (and also any letters at the end).

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

 

[Rick Rothstein]

Well that was easy. Thank you. Waiting for a response as well from JonXL - I failed to mention that the cells may contain an account number like "L123456789" and those are getting picked up. Also, it looks like some dates or phone numbers are being flagged as Y/True; which is not a big deal since we're down to auditing approximately 2k records from 400k. Thank you again.

Try this version of my function then...

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