Thread: Removing symbol(s) from table

Good morning... i have a table that has ssn structured:

11-1111111 or 111-11-1111

When I link to my table it doesn't pull anything up... is there a way that the "-" are removed and I only get 111111111???

Also the genius who designed this has two ssn in some of the fields:

111-11-1111/999-99-9999... don't know if something can be created that if field has more than 9 digits to move the remaining numbers to a new field???

Well any help is appreciated...

Try =SUBSTITUTE(A1,"-","") To remove the "-" sign

=SUBSTITUTE(LEFT(D4,FIND("/",D4)-1),"-","") To move the reminder.

For your first problem, I would suggest using the " Find and Replace " feature to replace the - with nothing in your ssn

Are the dashes in the TINs/SSNs hard coded or are they formatted in the table as an SSN? Look in your table first at the property for the field and let us know the format. If they are hard coded, then you will need to do some manipulation with some queries or an update through some VBA of your recordset. A little tedious but definately doable. Will this be an ongoing adventure or a one time clean up? If hard coded, please verify that the format is text.


If this is hardcoded as text, this will solve the first part of the problem to remove the dashes. Still working on separating the multi tax numbers. How large is your db, ie. how many records? How many with multi tax numbers.

SELECT SSN.ID, SSN.SSN, InStr(1,[SSN],"-") AS FindFirst, InStr(4,[SSN],"-") AS FindSecond, IIf([FindSecond]=0,Left([SSN],2) & Right([SSN],7),Left([SSN],3) & Mid([SSN],5,2) & Right([SSN],3)) AS Pure
FROM SSN;

You can uncheck the FindFirst and FindSecond Fields once you have this working and they will not appear in your query results. If you need to amend the table, you can then change this to an make table query and then delete the first table or you can make it an update query to change the values in the first table.

I would run a query that puts an expression in a column similar to

MulitTax: IIF(Len(SSN)>9, "Multi", "")

I would then add criteria to this field ="Multi" and manually pull them out and add new records. There appears to be not many and it may be the quickest way to clean this up. Did you try my query to take care of the other matter? Did it work as expected?

SSNSample.zipNo problem. I understand as we were all there at one time. I created a mini database that you can look at and then see how it all comes together. See attached. Then you can change your field names and table names as necessary.

See attachment below