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


G

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

Advertisements

B

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)
 
G

Guest

Hi Allan,

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

hth
regards from Brazil
Marcelo

"Allan" escreveu:
 
G

Guest

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

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

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
 
G

Guest

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

Advertisements

G

Guest

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

(e-mail address removed)
 
G

Guest

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

Advertisements

R

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top