VBA to replace 9 digit SSN with 4 digit - relative references

Hi, I've looked at previous threads about changing Social Security Numbers but haven't seen what I'm looking for. I need VBA code that I can distribute to multiple people who aren't great with excel in order to clean up 9 digit social security numbers and replace them with just the last 4 digits.

I need the code to work across a variety of files, and to replace the original value, not just add a new column with the RIGHT(4). I also need it to work based on just the selected active column, rather than a predefined column reference. I thought I could do it with text to columns, but it appears to break down with values that don't include leading 0's.

I've looked at the stuff in this thread excelforum.com/excel-programming-vba-macros/776671-shortening-a-social-security-number-to-last-4-digits.html but those seem to add a new column rather than replace the original.

Any help would be tremendously appreciated!

Thanks

thank you!

Here is another macro (no looping) that you can consider...

Please Login or Register  to view this content.

Note the line of code I highlighted in red above. That line of code formats the cells as Text in order to preserve any leading zeros. If you want the values as real numbers that you custom format with 0000 as the Type pattern, then remove that line of code.

No, I simply copied your posted code and then removed everything except for the first and last line (less typing for me and it saves me the trouble of having to figure out a name for the macro).

Actually, I just had an idea for an even more compact, loopless macro (it's a one-liner), but it does not preserve leading zeros so you will have to custom format the cell to provide them...

Please Login or Register  to view this content.