converting ssn's

  • Thread starter Thread starter tisaman
  • Start date Start date
T

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
 
tisaman said:
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
Click to expand...


=2999999999-(MID(A1,5,1)&LEFT(A1,3)&RIGHT(A1,4)&MID(A1,6,1))
 
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")
 
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 said:
=2999999999-(MID(A1,5,1)&LEFT(A1,3)&RIGHT(A1,4)&MID(A1,6,1))
Click to expand...

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 said:
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))
Click to expand...


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))
 
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))
 
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
Click to expand...


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
 
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))
 
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))
Click to expand...


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

--ron
 
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 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.
Click to expand...

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
 
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.
 
Back
Top