# Find Social Security Number in a String of Text

##### New Member
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

##### New Member
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.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Rick Rothstein

##### MrExcel MVP
1. @Rick Rothstein - what is the Excel formula to execute that VBA function?
Assuming cell A1 contains your text...

=HasSSN(A1)

##### New Member
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

##### Active Member
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).

Excel Formula:
=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

##### MrExcel MVP
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...
VBA Code:
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

Replies
9
Views
196
Replies
1
Views
101
Replies
0
Views
92
Replies
8
Views
98
Replies
2
Views
111

1,129,922
Messages
5,639,018
Members
417,062
Latest member
CM214

### 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.

### Which adblocker are you using?

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

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