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

[Guest]

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)

 

[Guest]

Hi Allan,

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

hth
regards from Brazil
Marcelo

"Allan" escreveu:

 

[Guest]

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

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

 

[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

 

[Guest]

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

 

[Guest]

Timm...I will experiment with your solution too...you guys are good. Thanks

 

[Guest]

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

(e-mail address removed)

 

[Guest]

thanks for the feedback.
regards
Marcelo

"Allan" escreveu:

 

[Guest]

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

 

[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.