Taking dashes out of SSN

jackson04

New Member
Joined
Jul 28, 2010
Messages
3
I have a long list of SSNs that I need to take the dashes and prefix of the cell. Here is an example:

What I have: 20/123-456-789
What I want: 123456789

I have over 1500 SSNs so by hand is the last option. Any suggestions?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
DO you want them fixed-in-place (vba), or the return in another column via formula?
what is "20/"?
Is"20/" (irrespective of being 20/, 21/, etc) always going to be 3 char's to strip off the front?
 

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
Does this work for you?
=RIGHT(SUBSTITUTE(A1,"-",""),9)

Assuming all of your data looks like your sample data, and your data is in Cell A1...
 

jackson04

New Member
Joined
Jul 28, 2010
Messages
3
To GTO: I would like to return the value in another column via formula if possible. The "20/" is a prefix that my organization uses to group SSN by families. This will be stripped off as well.

To pagrender: I will try that now.

Thanks to both of you for the quick reply
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,505
Messages
5,511,704
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top