Glenn wrote:
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


=2999999999-(MID(A1,5,1)&LEFT(A1,3)&RIGHT(A1,4)&MID(A1,6,1))

That will work if your SSN is entered as text. If entered as a 9-digit number
and formatted to add the dashes (000-00-0000):

=2999999999-(MID(A1,4,1)&LEFT(A1,3)&RIGHT(A1,4)&MID(A1,5,1))