Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SSN Formatting (2000)

    I'd like to reformat a column of Social Security numbers from ###-##-#### to ######### in a new column (i.e. take out the dashes). Any suggestions?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SSN Formatting (2000)

    Are the SSN's in the original column (A) numeric values formatted as 000-00-0000 or ([img]/forums/images/smilies/cool.gif[/img] text values that include the dashes?
    If (A), you can just use formulas of the form =P1 etc. (if the SSN's are in column P) and set number format to 000000000
    If ([img]/forums/images/smilies/cool.gif[/img], you can use formulas of the form =LEFT(P1,3)&MID(P1,5,2)&RIGHT(P1,4) etc. (if the SSN's are in column P)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Formatting (2000)

    Hans,
    <pre>=SUBSTITUTE(P1,"-","")</pre>


    will also replace all the dashes but without all the "rigamarole" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SSN Formatting (2000)

    Why do it the easy way if you can make it complicated? <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    Thanks!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Formatting (2000)

    I try to save the complicated way for those spreadsheets I will giving out to the Marketing people. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I love to see their eyes "glaze over" when I show them the formulas. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> <img src=/S/spook.gif border=0 alt=spook width=15 height=15> <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: SSN Formatting (2000)

    (Probably get censored but I can't resist.)

    A variation on old Dilbert cartoon that I like to use for presentations to Sales and Marketing is "A proctologist with a flashlight will now demonstrate where Sales Forecasts come from."
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Formatting (2000)

    I have used that same Dilbert cartoon when we have discussed Budgets and forecasts.

    Steve

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: SSN Formatting (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    If you still have it, I would love to get a copy. Call it a belated Christmas present, call it a tax deduction, call it charity work, but the topic of this cartoon just came up a few days ago, and I would love to post it on our intranet for our dept. to see.

    In return here is one for the Group...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Formatting (2000)

    I will see if I can dig it up. It has been several years and it was a transparency at the time. If I find it I will scan and post it.

    I think I originally found the "final frame" of the cartoon (with dogbert and the line) as a picture in the chapter start of one of his early books (the first or maybe second book) which I had borrowed.

    Steve

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Formatting (2000)

    I found the one I used (what I think is the last panel). As I mentioned I found just this picture in a book. I am not sure if I have ever seen (or if there was an entire strip).

    Steve

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Formatting (2000)

    In keeping with the "Dilbert theme" this post has turned into and my referencing to complicating it for marketing, here is another dilbert I found on this theme

    Steve

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Formatting (2000)

    Here is another "related one" I found in my search that you might enjoy.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •