A little bit shorter...

=2999999999-(MID(A1,5-(LEN(A1)=9),1)&TEXT(SUBSTITUTE(A1,MID(A1,4,4-2*(LEN(A1)=9)),""),"0000000")&MID(A1,6-(LEN(A1)=9),1))

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message ...
On Tue, 2 Dec 2008 12:05:07 -0800, tisaman
wrote:

because of HIPAA, i need an excel formula to convert the 9 digit ssn to a 10
digit number beginning with a 2

here is the formula, i for the life of me can't figure out the excel formula

Example:
ssn is 550-98-1815
remove the middle '98' and you get 5501815
take the '9' from '98' and place it at the front 95501815
take the '8' from '98' and place it at the end 955018158
subtract this number, 955018158 from 999,999,999
and you get 044981841 (note the significant leading zero(s), so ALWAYS have
9 digits)
put a '2' in front of this number, 044981841, and you get the member id of
2044981841 (always 10 characters)

if you can come up with the formula i would be forever grateful
thanks
tonia


This should work if the SSN is entered as either text with the hyphens, or as a
number:

=2999999999-(MID(TEXT(SUBSTITUTE(A1,"-",""),"000000000"),4,1)&
REPLACE(TEXT(SUBSTITUTE(A1,"-",""),"000000000"),4,2,"")&MID(
TEXT(SUBSTITUTE(A1,"-",""),"000000000"),5,1))

--ron