주민등록번호에서 생년월일 추출하는 엑셀 함수 활용법

엑셀을 활용해 주민등록번호에서 생년월일 데이터를 추출하는 방법을 소개합니다. LEFT, MID, DATE 함수를 활용하여 1900년대생과 2000년대생 모두 처리할 수 있는 수식을 알아보세요. 실무에 바로 적용 가능한 팁도 포함되어 있습니다.

엑셀에서 주민번호로 생년월일 추출하는 방법

엑셀에서 주민등록번호를 활용하여 생년월일 데이터를 추출하는 작업은 실무에서 자주 필요합니다. 주민등록번호의 앞 6자리는 생년월일 정보를 포함하고 있어 이를 적절히 가공하면 날짜 형식으로 변환할 수 있습니다. 이 글에서는 LEFT, MID, IF, DATE 함수를 활용해 1900년대생과 2000년대생 모두를 처리하는 방법을 단계별로 설명하겠습니다.

👇👇👇 내용이 더 궁금하시면 👇👇👇

설명 영상 바로가기

주민등록번호 구조 이해하기

주민등록번호는 다음과 같은 형식으로 구성됩니다:

예시: 701215-1234567
- 앞 6자리: 701215 (생년월일)
- 뒷자리 첫 번째 숫자:
  - 1 또는 2: 1900년대생
  - 3 또는 4: 2000년대생

따라서 생년월일을 정확히 추출하려면 뒷자리 첫 번째 숫자를 확인해 연도를 조정해야 합니다.

주요 함수 소개

엑셀에서 사용할 주요 함수는 다음과 같습니다:

LEFT: 문자열의 왼쪽에서 지정한 문자 수만큼 추출합니다.
- 예: =LEFT(A1,2) → 문자열 A1의 왼쪽 2자리 반환
MID: 문자열의 중간에서 지정한 위치와 길이만큼 추출합니다.
- 예: =MID(A1,3,2) → 문자열 A1의 세 번째 문자부터 두 자리 반환
DATE: 연도, 월, 일을 조합해 날짜 형식으로 변환합니다.
- 예: =DATE(2025,2,15) → "2025-02-15" 반환
IF: 조건에 따라 다른 값을 반환합니다.
- 예: =IF(A1>10,"크다","작다") → A1이 10보다 크면 "크다", 아니면 "작다" 반환

수식 작성 단계

기본적인 생년월일 추출

주민등록번호 입력
- A열에 주민등록번호를 입력합니다.
  - 예: 701215-1234567
연도 추출
- B열에 다음 수식을 입력합니다:
```
=IF(MID(A1,8,1)="1",1900+LEFT(A1,2),2000+LEFT(A1,2))
```
  이 수식은 뒷자리 첫 번째 숫자가 1 또는 2이면 1900을 더하고, 그렇지 않으면 2000을 더합니다.
월 추출
- C열에 다음 수식을 입력합니다:
```
=MID(A1,3,2)
```
  이 수식은 주민등록번호의 세 번째부터 두 자리를 가져옵니다.
일 추출
- D열에 다음 수식을 입력합니다:
```
=MID(A1,5,2)
```
  이 수식은 다섯 번째부터 두 자리를 가져옵니다.
날짜 조합
- E열에 최종적으로 DATE 함수를 사용해 날짜를 만듭니다:
```
=DATE(B1,C1,D1)
```
날짜 형식 설정
- E열 셀 서식을 "날짜"로 변경하면 날짜 형식으로 표시됩니다.

고급 사례: 2000년대생 처리

2000년대생과 1900년대생을 구분하려면 뒷자리 첫 번째 숫자를 기준으로 조건문을 추가해야 합니다. 아래는 이를 처리하는 통합 수식입니다:

=IF(OR(MID(A1,8,1)="1",MID(A1,8,1)="2"),DATE(1900+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)),DATE(2000+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)))

이 수식은 다음 조건을 따릅니다:

뒷자리 첫 번째 숫자가 1 또는 2이면 연도에 1900을 더함
그렇지 않으면 연도에 2000을 더함

외국인 등록번호 처리

외국인의 경우 뒷자리 첫 번째 숫자가 5, 6, 7, 8로 시작됩니다. 이를 처리하려면 조건문에 추가적인 조건을 넣어야 합니다:

=IF(OR(MID(A1,8,1)="5",MID(A1,8,1)="6",MID(A1,8,1)="7",MID(A1,8,1)="8"),"외국인","내국인")

필요 시 외국인 등록번호를 분리하거나 별도의 처리를 진행할 수 있습니다.

실무 팁

데이터가 많을 경우: 자동 채우기 핸들을 사용해 빠르게 수식을 복사하세요.
오류 처리: 잘못된 주민등록번호가 입력될 경우 오류가 발생할 수 있으므로 데이터 유효성 검사를 설정하세요.
날짜 형식 변경: yyyy-mm-dd 형식으로 표시하려면 셀 서식을 사용자 지정으로 변경하고 "yyyy-mm-dd"를 입력하세요.

자주 묻는 질문

Q: 주민등록번호가 없는 외국인의 생년월일은 어떻게 처리하나요?
A: 외국인의 경우 별도의 데이터베이스나 추가 정보를 통해 생년월일을 확인해야 합니다.

Q: 결과가 텍스트로 표시됩니다. 어떻게 해결하나요?
A: 셀 서식을 "날짜"로 변경하거나 DATEVALUE 함수를 사용해 날짜 형식으로 변환하세요.

Q: 주민등록번호가 없는 경우 오류를 방지하려면 어떻게 하나요?
A: IFERROR 함수를 사용해 오류 시 빈 값을 반환하도록 설정하세요:

=IFERROR(수식,"")

Q: 뒷자리가 잘못된 경우 어떻게 하나요?
A: 데이터 유효성 검사를 통해 올바른 형식의 주민등록번호만 입력되도록 제한하세요.

결론

엑셀에서는 다양한 함수를 활용해 주민등록번호에서 생년월일 데이터를 손쉽게 추출할 수 있습니다. 특히 LEFT와 MID 함수로 필요한 데이터를 분리하고 DATE 함수로 조합하면 날짜 형식으로 변환이 가능합니다. 실무에서는 데이터의 정확성을 유지하는 것이 중요하므로 오류 처리를 위한 추가적인 조건문이나 유효성 검사를 설정하는 것을 권장합니다.

저작자표시 (새창열림)

이것저것 리아의 공간