Extract SSN's from a free form text field...

I am trying to extract SSN's from a free form text field where the SSN
may appear in any portion of the field (beginning, end or middle of
the field).

The SSN will always be preceded by "SS", "SSN", or "SS#".
The SSN may be listed with or without dashes or spaces.
The SSN may be preceded by a dash or space or neither after "SS",
"SSN", or "SS#".


Examples:
Example #1 - SSN-123456789;
Example #2 - CE UNEMPLOYED SS# 987-65-4321 CBR 444-4444 SHADY COVE
Example #3 - SE RETIRED SS 876543219 SE RETIRED OTHER SVC WRKING IN CA

These are a few of the ways the SSN could appear in the field.

If the solution requires programming, please provide exact directions
and expectations.  I am not very good with programming.  I prefer a
formula if possible.
0
8/20/2004 5:46:56 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
814 Views

Similar Articles

[PageSpeed] 14

On 20 Aug 2004 10:46:56 -0700, vivinewell@yahoo.com (Viv) wrote:

>I am trying to extract SSN's from a free form text field where the SSN
>may appear in any portion of the field (beginning, end or middle of
>the field).
>
>The SSN will always be preceded by "SS", "SSN", or "SS#".
>The SSN may be listed with or without dashes or spaces.
>The SSN may be preceded by a dash or space or neither after "SS",
>"SSN", or "SS#".
>
>
>Examples:
>Example #1 - SSN-123456789;
>Example #2 - CE UNEMPLOYED SS# 987-65-4321 CBR 444-4444 SHADY COVE
>Example #3 - SE RETIRED SS 876543219 SE RETIRED OTHER SVC WRKING IN CA
>
>These are a few of the ways the SSN could appear in the field.
>
>If the solution requires programming, please provide exact directions
>and expectations.  I am not very good with programming.  I prefer a
>formula if possible.

It's easiest with VBA.

I took the approach that, if we remove the <space>'s and hyphens from your
string, a SSN could be uniquely identified by being the first consecutive
string of nine digits after an "SS".  If that is not the case, post back with
some more data.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then, from
the main menu bar on top:  Insert/Module and paste in the code below.

To use the function, in some cell enter the formula =SSN(cell_ref) where
cell_ref is the address of a string from which you want the SSN extracted.

e.g.  =SSN(A1)

The answer should appear as a string of nine digits (or maybe fewer if there
are leading zero's.

Format the result as ssn.  Format/Cells/Number/Special/Social Security Number

====================================
Option Explicit

Function SSN(rg As Range) As Long
    Dim i As Long
    Dim str As String
    Dim temp As String
    
    If rg.Count <> 1 Then Exit Function
    
    str = Replace(rg.Text, "-", "")
    str = Replace(str, " ", "")
    str = Right(str, Len(str) - InStr(1, str, "SS"))
    
    For i = 1 To Len(str) - 9
        temp = Mid(str, i, 9)
        If IsNumeric(temp) Then
            SSN = temp
            Exit Function
        End If
    Next i
End Function

=================================


--ron
0
ronrosenfeld (3122)
8/20/2004 6:33:53 PM
one way:

this works with the examples you gave. (Note that I used "SS ", i.e, 
with a space after the SS - in one of my substitutions. If that's not 
always to be the case, delete the space, but make sure you have no words 
prior to the SSN that have "SS" in them):

=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", 
""), "SSN", "$$$"), "SS#", "$$$"), "SS ", "$$$"), " ", ""), FIND("$$$", 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), 
"SSN", "$$$"), "SS#", "$$$"), "SS ", "$$$"), " ", ""))+3, 9)

In article <1c0643fe.0408200946.61447194@posting.google.com>,
 vivinewell@yahoo.com (Viv) wrote:

> I am trying to extract SSN's from a free form text field where the SSN
> may appear in any portion of the field (beginning, end or middle of
> the field).
> 
> The SSN will always be preceded by "SS", "SSN", or "SS#".
> The SSN may be listed with or without dashes or spaces.
> The SSN may be preceded by a dash or space or neither after "SS",
> "SSN", or "SS#".
> 
> 
> Examples:
> Example #1 - SSN-123456789;
> Example #2 - CE UNEMPLOYED SS# 987-65-4321 CBR 444-4444 SHADY COVE
> Example #3 - SE RETIRED SS 876543219 SE RETIRED OTHER SVC WRKING IN CA
> 
> These are a few of the ways the SSN could appear in the field.
> 
> If the solution requires programming, please provide exact directions
> and expectations.  I am not very good with programming.  I prefer a
> formula if possible.
0
jemcgimpsey (6723)
8/20/2004 6:34:09 PM
Hi!

Take your pick: one formula, one UDF:

http://tinyurl.com/6mjoh

Biff

>-----Original Message-----
>I am trying to extract SSN's from a free form text field 
where the SSN
>may appear in any portion of the field (beginning, end or 
middle of
>the field).
>
>The SSN will always be preceded by "SS", "SSN", or "SS#".
>The SSN may be listed with or without dashes or spaces.
>The SSN may be preceded by a dash or space or neither 
after "SS",
>"SSN", or "SS#".
>
>
>Examples:
>Example #1 - SSN-123456789;
>Example #2 - CE UNEMPLOYED SS# 987-65-4321 CBR 444-4444 
SHADY COVE
>Example #3 - SE RETIRED SS 876543219 SE RETIRED OTHER SVC 
WRKING IN CA
>
>These are a few of the ways the SSN could appear in the 
field.
>
>If the solution requires programming, please provide 
exact directions
>and expectations.  I am not very good with programming.  
I prefer a
>formula if possible.
>.
>
0
biffinpitt (3171)
8/20/2004 6:38:36 PM
Reply:

Similar Artilces:

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

2007 Forms
Hi - being a bit dim here I just made a simple form set it high and width to fairly small set min max buttons to both, border to thin, control box to yes but on running it fills the screen and no min max buttons. What am I missing. Allen Allen, Could it be that your forms are still set to 'tabbed' forms and you are looking for windowed forms. If that might be the case you can adjust this as follows: Office button -> access options -> current database -> check the radio button which states "overlapping windows". Close acces and start access again see if it ...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

printing a form
I have created a button to print a form using "printform". How can I control where the form prints out on the page? ...

i cannot read text in any message i receive
there was a previous thread on this but it didnt reach any conclusion. I have just installed Outlook2003 and can read plain text messages but not anything else. Does anyone know if this is an outlook issue or whether it could have something to do with norton antivirus? Help! parf <parf@discussions.microsoft.com> wrote: > there was a previous thread on this but it didnt reach any > conclusion. I have just installed Outlook2003 and can read plain text > messages but not anything else. Does anyone know if this is an > outlook issue or whether it could have something to do w...

In Excel how do I force text into next line before first is full?
,I am using Office XPPro (PC) but although I used Word a lot before I retired I am only now trying to do things in Excel (and incidentally Access). I want to put the membership records of a club into an Excel spreadsheet and to show each postal address divided (as it would be on an envelope) into several lines, some of them short. Since, when I am typing in data, pressing the Return key takes me to the next cell, not the next line, at present wherever I want a line break I am having to put in enough extra spaces to push the next word over the edge of the cell so that it wraps i...

need to get form property
hi, this is probably a very easy solution but I can't figure it out. I have Form A (frm_ARCall), subForm B (sfrm_ARCallInv) and Form C. From Form C, I want to change the Record Source property of subform B. I have tried many things but they have all failed. The latest is: Forms![frm_ARCall]![sfrm_ARCallInv]![Form].[RecordSource] = "fqry_ttARCallInv" NOTE: This line of code is on Form C which is a subform on another form (Form D). Please help -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201002/1 t...

Setting Focus on the Body (Message) field
How do I set the focus on the Body(message) field in outlook 2k? I have tried Set myInspector = Item.GetInspector Set myPage = myInspector.ModifiedFormPages("Message") myPage.Message.SetFocus 'mypage.body.setfocus but this does not seem to be working. Thanks ...

cannot publish form in O2003 cached mode
I cannot publish forms when running outlook 2003 in cached mode.It gives an error stating the org forms have not been selected for offline use.I am not working offline just using cached mode. Any ideas or is this just part of cached mode that you cannot do this. thanks niamh ...

Schedule+ Free Busy Information
I have inherited an Exchange Server that has some configuration issues with the public folders...most notibly the Schedule + Free Busy Information object. This server was originally on Exchange 5.5 and migrated to 2000. Additionally, the Exchange server was moved to a new domain. Long story short, I'm getting Event ID 8207 repeatedly in the server application log Error updating public folder with free-busy information on virtual machine <<servername>>. The error number is 0x80004005. I ran ADSI Edit to view detailed information on the public folder objects and found t...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Filter to select from various fields on opening a form
I have a form called TDC and want to filter data in the following fields on opening the form: DiaryAction (text field); DiaryCategory (text field); DiarySubCategory (text field); Company (text field); ContactType (text field); DiaryItemClosed (a Yes/No field). At the same time, I want the option not to select anything in each of these fields. Please could somebody give me the steps to do this. I am doing this in Access 2007, but am creating it as an Access 2003 database. Thank you for any help. I meant to say that I want this from dropdown boxes, but forgot to say becau...

how do i form 2 lines in 1 row?
I'm guessing you're looking to force text in a cell to wrap into the next line of the same cell. If that's true...... Edit the text and move the insertion point to where you want to wrap the text. Hold down the [Alt] key and press [Enter] Does that help? ---------------------------------- Regards, Ron Microsoft MVP (Excel) "MSEXCELROOKIE" <MSEXCELROOKIE@discussions.microsoft.com> wrote in message news:951AA059-1613-4A62-AAA6-7890AF6F84A6@microsoft.com... > In one cell type this is line one then Alt + ENTER then type this is line two. The Alt ...

Error opening form
Hi, I am trying to open a form from a switchboard and I get this error message: "MSACCESS exe has generated errors and will be closed by windows. You will need to restart the program. An error log is being created." I restart the computer and it's fixed. but it keeps happening. Does anyone know the solution? I have Microsoft Office 2000 thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1 There are many, many things that could be causing this. Work through the list in this article: Preventing corruption ...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Fill text boxes with data from another text box
I have a form that has two sections. Section 1 has orginator name, address, city state, zip. Section two has owner name, address, city, state, and zip. If orginator and owner are the same, I want to just hit a button or check mark to copy data from section one to section two. Can this be done, and how would you do it. Thanks for your help- Porkchop. Porkchop, To copy data from one control (and, hence, field, if the form control source is set to the name of a field in the form's underlying record source), put the following in the AfterUpdate event procedure of the checkbox: If ...

Anyone know of a free add-in that monitors COM input?
I have a bar code scanner that interfaces via RS232. My laptop doesn't have RS232 so I've got an RS232-to-USB adapter. Problem is, of course, Excel doesn't monitor COM ports, so I'm looking for an add-in (preferably free so I can use it sooner than later) that will monitor the COM port (like Hyperlink) and allow input into Excel. Any help is appreciated =) Thanks! ...

Repeating a formula in text
I'm concatenating two columns in an excel data file. The formula works, but won't replicate in the column to subsequent rows, all it will do is copy. I'm tearing my hair out - I've done it before but all of the sudden it won't work. Can you help? 1) What happens if you hit [F9] ? If this recalculates the correct results - then your Calculation option is set to "Manual". 2) Did you, by mistake, fix the References of the concatenated cells with $ [like: $A$1] ? Micky "Lyn" wrote: > I'm concatenating two columns in an ex...

Windows Forms 2.0
I have a complilation problem on a windows form in a Word VBA project. I included Excel since it's one of those other languages that uses Windows Forms instead of VBA forms. Every time I open Word or Excel I get all these stupid prompts about missing picture; compile problems; etc. I can get into the VBA project-- which surprises me-- but I can't open the Windows Form; since it sits there and complains about a 'missing picture'. I double click on the form name in Project Explorer (in the VBA IDE) and it just barfs and says 'cant find picture'. it won't compile; ...

how to draw text vertically?
for example, some software like Microsoft Word, when docking a toolbar to left or right, the text on button will be drawn vertically. In article <eShIl7FZGHA.3704@TK2MSFTNGP03.phx.gbl>, Bill Gates says... > for example, some software like Microsoft Word, when docking a toolbar to > left or right, the text on button will be drawn vertically. Basically you specify the angle in lfEscapement of the LOGFONT structure. Then call CreateFontIndirect with this structure and you'll get a rotated font of type HFONT. Use SelectObject to select the font and then you can use TextO...

Text Wrap
I have text wrap on a cell with lots of text and I use "Alt Enter" to create new lines within the cell. The last two entries in the cell are not wrapping though the first four entries within the cell wrap just fine. Is there a way to get the last two entries within the cell to text wrap? Thanks! Is the rowheight big enough? And if those last two entries are really long, then maybe a few more alt-enters to break them up???? Craig wrote: > > I have text wrap on a cell with lots of text and I > use "Alt Enter" to create new lines within the cell. The > l...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

entering data on form
I am trying to help a local organization with a problem. First they have no funds and cannot purchase Access. That being said, I need to create some system so that 2-3 different people can enter data into a spreadsheet and not screw things up each time. Being able to enter data on a form that will populate a worksheet will be the answer. They won't need to do any typical database stuff, just enter information and make sure it gets to the workbook. Most of these people will nto have access to or need to see the actual spreadsheet, just enter information. This spreadsheet will be abou...