Find Social Security Number in a String of Text

SoldOnTheRoad

New Member
Joined
Jan 3, 2017
Messages
12
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 3, 2017
Messages
12
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.

StringExpected ResultNote
The cow jumped over the moonNNo numbers
The cow 023-45-6789 over the moonYYes - finds SSN in ###-##-#### format
The cow 123456789 over the moonYYes - finds SSN in ######### format
The cow 1/1/21 over the moonNNo - doesn't meet length or format
The cow 123/45/6789 over the moonYYes - finds SSN in ###/##/#### format
The cow 01-01-2001 over the moonNNo - doesn't meet length or format
On 1/1/2001. The cow jumped over the moonNNo - doesn't meet length or format
On 1/1/2001. The cow 123-45-6789 over the moonYYes - finds SSN in ###-##-#### format; ignores date
Customer 9900990099009900. The cow 123456789 over the moonYYes - finds SSN in ######### format; ignores string >9 numbers
On 1/1/2021, the cow 1/1/21 over the moonNNo - doesn't meet length or format
9-9-21. The cow 123/45/6789 over the moonYYes - finds SSN in ###/##/#### format; ignores date
9/9/21. The cow 01-01-2001 over the moonNNo - doesn't meet length or format
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

VBA Code:
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):

Excel Formula:
=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
ABCD
1StringExpected ResultNoteUDF
2The cow jumped over the moonNNo numbersN
3The cow 023-45-6789 over the moonYYes - finds SSN in ###-##-#### formatY
4The cow 123456789 over the moonYYes - finds SSN in ######### formatY
5The cow 1/1/21 over the moonNNo - doesn't meet length or formatN
6The cow 123/45/6789 over the moonYYes - finds SSN in ###/##/#### formatY
7The cow 01-01-2001 over the moonNNo - doesn't meet length or formatN
8On 1/1/2001. The cow jumped over the moonNNo - doesn't meet length or formatN
9On 1/1/2001. The cow 123-45-6789 over the moonYYes - finds SSN in ###-##-#### format; ignores dateY
10The cow 123456789 over the moonYYes - finds SSN in ######### formatY
11On 1/1/2021, the cow 1/1/21 over the moonNNo - doesn't meet length or formatN
129-9-21. The cow 123/45/6789 over the moonYYes - finds SSN in ###/##/#### format; ignores dateY
139/9/21. The cow 01-01-2001 over the moonNNo - doesn't meet length or formatN
14Customer 9900990099009900. The cow over the moonNNo - ignores string >9 numbersN
Sheet1
Cell Formulas
RangeFormula
D2:D14D2=IF(OR(islike(A2,"*[!0-9]###[-/]##[-/]####[!0-9]*"),islike(A2,"*[!0-9]#########[!0-9]*")),"Y","N")
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,173
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

FWIW My take was this....

VBA Code:
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
AB
1
2The cow jumped over the moonN
3The cow 123-45-6789 over the moonY
4The cow 123456789 over the moonY
5The cow 1/1/21 over the moonN
6The cow 123/45/6789 over the moonY
7The cow 01-01-2001 over the moonN
8On 1/1/2001. The cow jumped over the moonN
9On 1/1/2001. The cow 123-45-6789 over the moonY
10Customer 9900990099009900. The cow 123456789 over the moonY
11On 1/1/2021, the cow 1/1/21 over the moonN
129-9-21. The cow 123/45/6789 over the moonY
139/9/21. The cow 01-01-2001 over the moonN
Sheet9
Cell Formulas
RangeFormula
B2:B13B2=SSN(A2)
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@Snakehips, will that catch "my ssn is 123-45-6789."? (Note the period at the end.)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
I think this function will work correctly...
VBA Code:
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.
 
Last edited:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,173
Office Version
  1. 2013
Platform
  1. Windows
I think this function will work correctly...
VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
I believe this fixes my function so that it will work correctly...
VBA Code:
Function HasSSN(S As String) As Boolean
  HasSSN = " " & S & " " Like "*[!0-9]###[/-]##[/-]####[!0-9]*" Or " " & S & " " Like "*[!0-9]#########[!0-9]*"
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,130,144
Messages
5,640,388
Members
417,140
Latest member
whiteprose

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
Top