Assume social security number is in A1:
=concatenate(left(a1,3),"-",mid(a1,5,2),"-",right(a1,4))

And be aware that XL is not a secure program and social security numbers
should not be stored in it.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


" wrote:

I created a CSV file from a medical database program at work. I
converted the csv file into an excel spreadsheet (A.xls). The social
security number format in this file defaulted to ###-##-####. The
number of records totalled ~2100.

I received another excel file (B.xls) where the social security number
format is ####### (no dashes). The number of records totalled ~1400.

That means ~700 records are not showing up on B.xls.

I want to identify which 700 from A.xls is not on B.xls. I created
formulas using VLOOKUP and INDEX & Match and neither worked.

What I found is if I manually added the dashes to a record in B.xls
(as well as the extra zeroes at the beginning of the social) then the
formula worked. What this is telling me is I have a format issue
between the two files. It tells me the only solution I am aware of is
to manually add the dashes to the cell to make the formulas work.

(I've tried the format cell -- SSN ###-##-#### function already. The
data input doesn't change, only the "look" on the spreadsheet changes;
so the formula doesn't match the two types.)

Do I need to manually add the dashes to every SSN or is there an
easier way? Thanks