converting ssn's

[tisaman]

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

 

[Glenn]

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

 

[muddan madhu]

try this

cell A1 has number then in cell B1 put this formula
=2&TEXT(999999999-VALUE(LEFT(MID(A1,FIND("-",A1)+1,2),1)&SUBSTITUTE
(A1,"-"&MID(A1,FIND("-",A1)+1,2)&"-","")&RIGHT(MID(A1,FIND
("-",A1)+1,2),1)),"000000000")

 

[Mike H]

Hi,

With your original number in A1 try this

=2&TEXT(999999999-(MID(A1,FIND("-",A1)+1,1)&LEFT(A1,3)&RIGHT(A1,4)&MID(A1,FIND("-",A1)+2,1)),"000000000")

Mike

 

[Glenn]

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

 

[Glenn]

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

Actually, SSN's can start with zeros (but only 2). If you enter

1234567

and format it as

001-23-4567

use this:

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

 

[Rick Rothstein]

Here is my contribution to the solution pool...

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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

 

[Ron Rosenfeld]

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

Yours does not seem work properly with numeric SSN entries that begin with 0 or
00.

--ron

 

[Rick Rothstein]

I think it works with them if the cell is formatted as text (or if the entry starts with an apostrophe)... I assumed if there would be leading numbers, then the cell would be formatted as text to preserve them.

 

[Ron Rosenfeld]

I think it works with them if the cell is formatted as text (or if the entry starts with an apostrophe)... I assumed if there would be leading numbers, then the cell would be formatted as text to preserve them.

I agree with that.

However, when I wrote "numeric entries", I meant entries that were entered as
numbers, not as text.

Especially since SSN is one of the built-in special formats, I felt it
important to account for this.
--ron

 

[Rick Rothstein]

Okay, yes, I can see that. Good point. Even without using the SSN built-in format, the OP might have simply use a number Custom Formatted as 000-00-0000 for the same effect.