엑셀tip] 주민등록번호, 외국인등록번호 검증 수식

A1 셀에 데이터를 입력한 경우

주민등록번호(외국인번호) 검증 수식.xlsx

0.01MB

청소년캠프 주민등록번호 검증_외국인포함.xlsx

0.02MB

(입력양식: 0000000000000, '-'없이 숫자만 입력)

 

내국인 주민등록번호 검증 엑셀 수식
=IF(RIGHT(A1,1)=(RIGHT(11-MOD(MID(A1,1,1)*2+MID(A1,2,1)*3+MID(A1,3,1)*4+MID(A1,4,1)*5+MID(A1,5,1)*6+MID(A1,6,1)*7+MID(A1,7,1)*8+MID(A1,8,1)*9+MID(A1,9,1)*2+MID(A1,10,1)*3+MID(A1,11,1)*4+MID(A1,12,1)*5,11),1)),"O","X")

외국인 등록번호 검증 엑셀 수식
=IF(RIGHT(A1,1)=(RIGHT(13-MOD(MID(A1,1,1)*2+MID(A1,2,1)*3+MID(A1,3,1)*4+MID(A1,4,1)*5+MID(A1,5,1)*6+MID(A1,6,1)*7+MID(A1,7,1)*8+MID(A1,8,1)*9+MID(A1,9,1)*2+MID(A1,10,1)*3+MID(A1,11,1)*4+MID(A1,12,1)*5,13),3)),"O","X")