| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting ssn's
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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") On Dec 3, 1:05*am, 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 of2044981841(always 10 characters) if you can come up with the formula i would be forever grateful thanks tonia |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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 "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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
Glenn wrote:
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)) 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)) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
Here is my contribution to the solution pool...
=2999999999-(MID(A1,5,1)&TEXT(SUBSTITUTE(A1,MID(A1,4,4),""),"0 000000")&MID(A1,6,1)) -- Rick (MVP - Excel) "tisaman" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
On Tue, 2 Dec 2008 22:58:04 -0500, "Rick Rothstein"
wrote: 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) Yours does not seem work properly with numeric SSN entries that begin with 0 or 00. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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.
-- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... On Tue, 2 Dec 2008 22:58:04 -0500, "Rick Rothstein" wrote: 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) Yours does not seem work properly with numeric SSN entries that begin with 0 or 00. --ron |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
On Wed, 3 Dec 2008 00:08:23 -0500, "Rick Rothstein"
wrote: 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. -- Rick (MVP - Excel) 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting ssn's
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.
-- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... On Wed, 3 Dec 2008 00:08:23 -0500, "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trim all but the last four digits off a list of SSN's. | Excel Discussion (Misc queries) | |||
| Converting a date to a text field w/o converting it to a julian da | Excel Worksheet Functions | |||
| Some of my SSN's are being treated as formulas | Excel Discussion (Misc queries) | |||
| Converting USD$ to AUD$ | Excel Worksheet Functions | |||
| Converting QIF to XLS | Excel Discussion (Misc queries) | |||
Linear Mode
