Hello all.
I am attempting to come up with a formula or something that will take social security numbers downloaded from another system in this format of 123-45-6789(Where I cannot fix the format) and automatically change them this format:
xxx-xx-6789
I need to do this for an entire column of numbers for 125 prospective customers.
Any help would be appreciated.
Sorry if this has been posted already, but could not find it.
Thanks.
Dave
Last edited by davidingilbert; 11-01-2012 at 02:02 PM.
Assume SSN is in A1. In B1 type =REPLACE(A1,1,11,"xxx-xx-"&RIGHT(A1,4))
and copy down.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Alan - Thank you.....
Stay warm in Chicago!
One more question. When I run the program, it works great. But when I delete the column that the original SS# is in, I get the "#ref!" message in the column I designated with the format of 123-45-xxxx. How do I go about not getting the "!ref!" message. Should I use a macro and if so, same program?
before you delete the column with the original data in it, copy the column with the formula in it it, and paste values to itself. you canthen delete the original data
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
When I do that, my format goes from 123-45-xxxx to -xxxx
Use Paste Special-->Values. I would do in an adjacent column and then delete the other columns.
Ok one more thing...
in my report, I do have it where sometimes social security numbers do not populate for whatever reason.
Aside from the formula I have to change the last four digits to XXXX, how would I do the formula so that if the SS# field is blank, the new field with the XXXX is also blank.
I know it is an "=If....", but can't figure out the finer details.
Thanks.
Dave
=if(A1="", "",REPLACE(A1,1,11,"xxx-xx-"&RIGHT(A1,4)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks