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
 

SoldOnTheRoad

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

SoldOnTheRoad

New Member
Joined
Jan 3, 2017
Messages
12
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
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,979
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

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