How do I add a 0 and 2 dashes to a truncated soc. sec.# in a cell

[Allan]

I have a spreadsheet that has social security numbers (SSN) in a column. The
numbers are missing the leading zeros and the two dashes for the SSN format
(i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell,
the you see only 8 numbers, but when you format you of course see the SSN
format...000-00-0000. I need to add the leading zero and then the two dashes
(this because I am transferring the numbers to another software package).

My data set is large..manual entry will take forever.

Any ideas are welcome.

[Marcelo]

Hi Allan,

try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4)

hth
regards from Brazil
Marcelo

"Allan" escreveu:

> I have a spreadsheet that has social security numbers (SSN) in a column. The
> numbers are missing the leading zeros and the two dashes for the SSN format
> (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell,
> the you see only 8 numbers, but when you format you of course see the SSN
> format...000-00-0000. I need to add the leading zero and then the two dashes
> (this because I am transferring the numbers to another software package).
>
> My data set is large..manual entry will take forever.
>
> Any ideas are welcome.

[Bob Phillips]

Add this in another column

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

copy down and use this data

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Allan" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet that has social security numbers (SSN) in a column.
The
> numbers are missing the leading zeros and the two dashes for the SSN
format
> (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the
cell,
> the you see only 8 numbers, but when you format you of course see the SSN
> format...000-00-0000. I need to add the leading zero and then the two
dashes
> (this because I am transferring the numbers to another software package).
>
> My data set is large..manual entry will take forever.
>
> Any ideas are welcome.

[tim m]

After a bit of experimentaion this seemed to do the job.

=CONCATENATE("0",LEFT(A1,2),"-",MID(A1,3,2),"-",RIGHT(A1,4))

"Allan" wrote:

> I have a spreadsheet that has social security numbers (SSN) in a column. The
> numbers are missing the leading zeros and the two dashes for the SSN format
> (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell,
> the you see only 8 numbers, but when you format you of course see the SSN
> format...000-00-0000. I need to add the leading zero and then the two dashes
> (this because I am transferring the numbers to another software package).
>
> My data set is large..manual entry will take forever.
>
> Any ideas are welcome.

[jetted]

You may want to try this

Sub add_zero_dash()

const_dash = "-"
'assuming data is in column A
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To rowcount
Range("a" & i).Select
entirename = ActiveCell.Value
check_len = Len(entirename)
If check_len = 8 Then
new_val = "0" & entirename
Selection.NumberFormat = "@"
ActiveCell.Value = new_val
new_val_first = Left(new_val, 3)
new_val_last = Right(new_val, 3)
new_val_mid = Mid(new_val, 4, 3)
ActiveCell.Value = new_val_first & const_dash & new_val_mid & const_dash & new_val_last
End If
Next
End Sub

[Allan]

Thanks...you gave me the seed (and possibly the fruit) of my solution. You
guys are good!

"Marcelo" wrote:

> Hi Allan,
>
> try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4)
>
> hth
> regards from Brazil
> Marcelo
>
> "Allan" escreveu:
>
> > I have a spreadsheet that has social security numbers (SSN) in a column. The
> > numbers are missing the leading zeros and the two dashes for the SSN format
> > (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell,
> > the you see only 8 numbers, but when you format you of course see the SSN
> > format...000-00-0000. I need to add the leading zero and then the two dashes
> > (this because I am transferring the numbers to another software package).
> >
> > My data set is large..manual entry will take forever.
> >
> > Any ideas are welcome.

[Allan]

Timm...I will experiment with your solution too...you guys are good. Thanks
"tim m" wrote:

> After a bit of experimentaion this seemed to do the job.
>
> =CONCATENATE("0",LEFT(A1,2),"-",MID(A1,3,2),"-",RIGHT(A1,4))
>
> "Allan" wrote:
>
> > I have a spreadsheet that has social security numbers (SSN) in a column. The
> > numbers are missing the leading zeros and the two dashes for the SSN format
> > (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell,
> > the you see only 8 numbers, but when you format you of course see the SSN
> > format...000-00-0000. I need to add the leading zero and then the two dashes
> > (this because I am transferring the numbers to another software package).
> >
> > My data set is large..manual entry will take forever.
> >
> > Any ideas are welcome.

[Allan]

Marcelo...your solution is excellent!...Thanks a bunch...I would love to keep
in touch.

[email protected]

"Marcelo" wrote:

> Hi Allan,
>
> try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4)
>
> hth
> regards from Brazil
> Marcelo
>
> "Allan" escreveu:
>
> > I have a spreadsheet that has social security numbers (SSN) in a column. The
> > numbers are missing the leading zeros and the two dashes for the SSN format
> > (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell,
> > the you see only 8 numbers, but when you format you of course see the SSN
> > format...000-00-0000. I need to add the leading zero and then the two dashes
> > (this because I am transferring the numbers to another software package).
> >
> > My data set is large..manual entry will take forever.
> >
> > Any ideas are welcome.

[Marcelo]

thanks for the feedback.
regards
Marcelo

"Allan" escreveu:

> Marcelo...your solution is excellent!...Thanks a bunch...I would love to keep
> in touch.
>
> [email protected]
>
> "Marcelo" wrote:
>
> > Hi Allan,
> >
> > try ="0"&left(a2,2)&"-"&mid(a2,3,2)&"-"&right(a2,4)
> >
> > hth
> > regards from Brazil
> > Marcelo
> >
> > "Allan" escreveu:
> >
> > > I have a spreadsheet that has social security numbers (SSN) in a column. The
> > > numbers are missing the leading zeros and the two dashes for the SSN format
> > > (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the cell,
> > > the you see only 8 numbers, but when you format you of course see the SSN
> > > format...000-00-0000. I need to add the leading zero and then the two dashes
> > > (this because I am transferring the numbers to another software package).
> > >
> > > My data set is large..manual entry will take forever.
> > >
> > > Any ideas are welcome.

[tim m]

This is far easier than my frankenfunction solution. :O)
I often find better ways to do things on this forum.

"Bob Phillips" wrote:

> Add this in another column
>
> =TEXT(A1,"0000-000-0000")
>
> copy down and use this data
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Allan" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spreadsheet that has social security numbers (SSN) in a column.
> The
> > numbers are missing the leading zeros and the two dashes for the SSN
> format
> > (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the
> cell,
> > the you see only 8 numbers, but when you format you of course see the SSN
> > format...000-00-0000. I need to add the leading zero and then the two
> dashes
> > (this because I am transferring the numbers to another software package).
> >
> > My data set is large..manual entry will take forever.
> >
> > Any ideas are welcome.
>
>
>

[Ragdyer]

Have you tried formatting the cells as SSN?

If your column of numbers are true numbers,
Select the column, then,
<Format> <Cells> <Number> tab,
Click on "Special" in the left window,
Then click on "Social Security Number" in the right window.

This will *add* leading 0's and dashes.

If your numbers are 'Text',
Right click in a new, empty, unused cell, and choose "Copy",
Select the column of numbers and right click in the selection,
Choose "Paste Special",
Then click on "Add", then <OK>.

You NOW have true numbers, so you can now format as above, to SSN.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Allan" <[email protected]> wrote in message
news:[email protected]...
>
> Timm...I will experiment with your solution too...you guys are good.
> Thanks
> "tim m" wrote:
>
>> After a bit of experimentaion this seemed to do the job.
>>
>> =CONCATENATE("0",LEFT(A1,2),"-",MID(A1,3,2),"-",RIGHT(A1,4))
>>
>> "Allan" wrote:
>>
>> > I have a spreadsheet that has social security numbers (SSN) in a
>> > column. The
>> > numbers are missing the leading zeros and the two dashes for the SSN
>> > format
>> > (i.e. the number is XXXXXXXX instead of 0XX-XX-XXXX. If you look in the
>> > cell,
>> > the you see only 8 numbers, but when you format you of course see the
>> > SSN
>> > format...000-00-0000. I need to add the leading zero and then the two
>> > dashes
>> > (this because I am transferring the numbers to another software
>> > package).
>> >
>> > My data set is large..manual entry will take forever.
>> >
>> > Any ideas are welcome.