Adding dashes to a column of SSNs

J.E. McGimpsey

unread,

Jan 2, 2003, 4:29:15 PM1/2/03

to

You could use

Format/Cells/Number/Special SSN

Note that this only adds dashes to the display.

You could use string functions to add them to the values, e.g.:

A1: 123456789
B1: =LEFT(A1,3) & "-" & MID(A1, 4, 2) & "-" & RIGHT(A1, 4)

In article <079001c2b2a5$3dc27fa0$89f82ecf@TK2MSFTNGXA01>, Brian
<alle...@bellsouth.net> wrote:

> Is there a preferred way to add dashes to a listing of
> SSNs without them that are contained in one column. I've
> tried using csv and prn saves along with re-imports
> without any luck yet.
>
> Thanks

Nick Hodge

unread,

Jan 2, 2003, 4:26:37 PM1/2/03

to

Brian

Does an SSN have a set number of dashes at set places?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, UK
nick_...@btinternet.com

--- A courtesy email copy may have been sent
with this reply to the original poster please
follow up in the newsgroup only ---

"Brian" <alle...@bellsouth.net> wrote in message
news:079001c2b2a5$3dc27fa0$89f82ecf@TK2MSFTNGXA01...

Brian

unread,

Jan 2, 2003, 4:23:49 PM1/2/03

to

Brian

unread,

Jan 2, 2003, 4:37:46 PM1/2/03

to

Nick,

Each SSN in the SSN column is listed as 123456789, but to
import them to a benefits system they must be in the
format 123-45-6789. There are over 3000 SSNs in this
column. Once I get them in this format, I can create the
csv file.

Thanks

>.
>

Brian

unread,

Jan 2, 2003, 4:57:53 PM1/2/03

to

The string function looks like it will do the job. I'll
give it a try.

Thank you very much!

>.
>

Brian

unread,

Jan 2, 2003, 5:51:08 PM1/2/03

to

I tried this and I can not get it to work. I started to
review help and tried the simple example from the excel
documentation CONCATENATE("Total ", "Value") and could not
get this to work either (also tried =CONCAT..). I only
see exactly what I typed after entering. The field I
originally tried the function on was a formatted as text,
and I tried it on a new sheet without any luck. Regular
formulas such as SUM work fine.

Any idea why this would not work?

Thanks

>-----Original Message-----
>You could use
>
> Format/Cells/Number/Special SSN
>
>Note that this only adds dashes to the display.
>
>You could use string functions to add them to the values,
e.g.:
>
>A1: 123456789

>B1: =LEFT(A1,3) & "-" & MID(A1, 4, 2) & "-" & RIGHT
(A1, 4)
>
>
>In article <079001c2b2a5$3dc27fa0

$89f82ecf@TK2MSFTNGXA01>, Brian
><alle...@bellsouth.net> wrote:
>
>> Is there a preferred way to add dashes to a listing of
>> SSNs without them that are contained in one column.
I've
>> tried using csv and prn saves along with re-imports
>> without any luck yet.
>>
>> Thanks

>.
>

J.E. McGimpsey

unread,

Jan 2, 2003, 6:10:13 PM1/2/03

to

Well, I guess it depends on what you mean by "doesn't work". Does it
give you an error? The wrong text value?

It works for me as written. The number being entered as text should be
fine, too, unless there are leading or trailing spaces in which case
you can use:

=LEFT(TRIM(A1),3) & "-" & MID(TRIM(A1), 4, 2) & ...

You could also use:

=TEXT(TRIM(A1), "000-00-0000")

In article <034601c2b2b1$70936aa0$d4f82ecf@TK2MSFTNGXA11>, Brian

Ken Wright

unread,

Jan 2, 2003, 6:12:16 PM1/2/03

to

You must format the cells as something other than text before entering the
formula, else it will think your formula is simply text.

Regards
Ken.......................

"Brian" <alle...@bellsouth.net> wrote in message

news:034601c2b2b1$70936aa0$d4f82ecf@TK2MSFTNGXA11...

Brian

unread,

Jan 2, 2003, 7:10:17 PM1/2/03

to

I did try changing the format of the cell back to general
without any luck. I had to format the column as text to
retain the leading zeros on some SSNs. When I press
enter, the function remains as text. I also tried
copying the formula to a new sheet which should default
to general.

Could the CONCATENATE function require any add-in's?

Thanks

>.
>

Brian

unread,

Jan 2, 2003, 10:11:12 PM1/2/03

to

Both of your examples work great from Excel while at
home. I'll have to check out my setup back at work.

Thanks!

>.
>

Dave Peterson

unread,

Jan 2, 2003, 10:13:13 PM1/2/03

to

Formatting them back to general isn't enough.

But if you're willing to make them all numbers, you can select an empty cell,
copy it.

Then select your range of ssn's (whole column).
And Edit|Paste special|click on Add in the Operation.

Then you should be able to use the special format (SSN) or the =text() formula.

--

Dave Peterson
ec3...@msn.com

Ken Wright

unread,

Jan 3, 2003, 2:46:25 AM1/3/03

to

If all your text 'numbers' are in Column A and Col A is
formatted as text then now format Col B as general or
number. Now put the formulas into the cells in Col B
(Don't copy and paste from a text format cell where you
already have the formula, unless you edit the cell, copy
the formula, go into the cell in Col B and then paste the
formula). Once you have one working simply copy and paste
down.

Regards
Ken.................

>.
>