Reformat SSN to add dashes between numbers

[wheatgrass]

I have a list of 375 social security numbers. I want to reformat the numbers to add a dash in between the numbers so I can run a query on them. Right now they are showing as 123456789. I want them to show as 123-45-6789. Does anyone know of a fast and easy way to make this happen instead of me clicking in each cell and adding a dash between the numbers?
Thanks.

[abishekmouli88]

Hi,

Format it as Social Security Number:

Format->Special->Social Security Number

Regards,
Abi

[TMS]

I'm guessing that reformatting will only change the display of the SSN. Do you need actual hyphens inserting?

Regards

[wheatgrass]

Yes, I need actual hyphens inserting between numbers.

[jaslake]

Hi wheatgrass

Insert a new column (assume Column B) with your original numbers in Column A. Enter this formula in Column B and drag down

Please Login or Register  to view this content.

Copy/Paste Special --> Values from Column B to Column A. Delete Column B.

[TMS]

One way:

Please Login or Register  to view this content.

Adjust the startrow and the column as required; it's currently set up to operate on column 1 (A) and start at row 1.

Regards

[abishekmouli88]

Hello,

try executing this code

Please Login or Register  to view this content.

Regards,
Abi

[wheatgrass]

Thanks, worked like a charm.

[TMS]

Thanks, worked like a charm.

Not sure which solution you are referring to.

However, if your question has been answered, please mark your thread as solved. See my signature for details or the FAQ.

Regards

[wkelley]

Hi,

So this does work for 2007 I have done it will it also work for 2010 using the same steps?

wk

[TMS]

You're post might well be considered hijacking ... but give it a go, what have you got to lose?

Regards, TMS

[wkelley]

Hi TMS,

Not sure what the "considered hijacking" means but I'm soory if I have stepped on any toes.

Back on 6-12-11 there was a posting:

Hi,

Format it as Social Security Number:

Format->Special->Social Security Number

Regards,............

I mentioned these directions do work with Excel version 2007, because I have done it. I was wondering if these same steps will work with 2003 & 2010?

Thanks, wk

[TMS]

Hi WK ... no toes hurt, or even bruised.

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.


... is what I meant, just to quote the rule. Reviving an 18 month old post isn't usually such a good idea, either


That said, there's no code there that will cause concern in 2003 or 2010.

But, I repeat the question: "why not give it a go, what have you got to lose?"

The worst that can happen is that you would get an error message.


Regards, TMS

[TMS]

@wkelley: Thanks for the rep.