카테고리 없음

엑셀 함수로 주민번호에서 생년월일 추출하는 방법 총정리

시오공간 2025. 4. 5. 01:01

엑셀에서 주민번호를 이용해 생년월일을 추출하는 다양한 방법을 알아보세요. MID, LEFT 함수부터 DATE 함수까지 활용하여 2000년대생을 포함한 모든 경우의 생년월일을 정확하게 추출하는 방법을 상세히 설명합니다. 실무에서 바로 활용 가능한 엑셀 함수 기법을 배워보세요.

주민번호의 구조와 생년월일 정보

주민등록번호는 우리 생활에서 매우 중요한 개인 식별 정보입니다. 13자리로 구성된 주민번호에서 앞 6자리는 생년월일을 의미합니다. 앞의 두 자리는 태어난 연도, 세 번째와 네 번째 자리는 태어난 월, 다섯 번째와 여섯 번째 자리는 태어난 날짜를 나타냅니다. 그리고 7번째 자리(하이픈 뒤 첫 번째 숫자)는 성별과 출생 연대를 구분하는 중요한 정보입니다.

예를 들어 '700228-1**'라는 주민번호가 있다면, 70년 2월 28일생이며 1900년대 출생 남성임을 알 수 있습니다. 이러한 정보를 엑셀에서 자동으로 추출하여 활용하는 방법을 알아보겠습니다.

기본 함수를 이용한 생년월일 추출 방법

MID 함수와 LEFT 함수 활용하기

주민번호에서 생년월일을 추출하기 위해 가장 기본적으로 사용되는 함수는 MID와 LEFT 함수입니다. 이 함수들은 문자열에서 특정 위치의 문자를 추출하는 데 사용됩니다.

  • LEFT 함수: 문자열의 왼쪽에서부터 지정된 수의 문자를 추출합니다.
  • MID 함수: 문자열의 지정된 위치에서 시작하여 지정된 수의 문자를 추출합니다.

예를 들어, A1 셀에 주민번호가 있다고 가정할 때:

  • 연도 추출: =LEFT(A1,2)
  • 월 추출: =MID(A1,3,2)
  • 일 추출: =MID(A1,5,2)

이렇게 추출한 정보에 년, 월, 일을 표시하기 위해 "&" 연산자를 사용하여 문자열을 합칠 수 있습니다.

=LEFT(A1,2)&"년 "&MID(A1,3,2)&"월 "&MID(A1,5,2)&"일"

하지만 이 방법은 완전한 연도를 표시하지 못한다는 한계가 있습니다. 예를 들어 70년생이라면 1970년인지 2070년인지 구분할 수 없습니다.

DATE 함수를 활용한 완전한 생년월일 추출

DATE 함수는 연, 월, 일 값을 입력받아 날짜 형식으로 변환해주는 함수입니다. 구조는 다음과 같습니다:

=DATE(Year, Month, Day)

주민번호에서 추출한 연, 월, 일 정보를 DATE 함수의 인수로 사용하면 날짜 형식의 생년월일을 얻을 수 있습니다. 하지만 주민번호의 앞 두 자리만으로는 1900년대생인지 2000년대생인지 구분할 수 없습니다.

2000년대생 포함한 생년월일 추출 방법

2000년대생과 1900년대생을 구분하기 위해서는 주민번호의 7번째 자리(하이픈 뒤 첫 번째 숫자)를 확인해야 합니다.

  • 1 또는 2: 1900년대생 (1: 남자, 2: 여자)
  • 3 또는 4: 2000년대생 (3: 남자, 4: 여자)
  • 5 또는 6: 1900년대생 외국인 (5: 남자, 6: 여자)
  • 7 또는 8: 2000년대생 외국인 (7: 남자, 8: 여자)

이 정보를 활용하여 IF 함수와 DATE 함수를 조합하면 정확한 생년월일을 추출할 수 있습니다.

IF 함수와 DATE 함수 조합하기

주민번호가 A1 셀에 있다고 가정할 때, 다음과 같은 수식을 사용할 수 있습니다:

=IF(OR(MID(A1,8,1)="1",MID(A1,8,1)="2",MID(A1,8,1)="5",MID(A1,8,1)="6"),
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. 주민번호의 8번째 자리(하이픈 포함)가 1, 2, 5, 6 중 하나인지 확인합니다.
  2. 만약 그렇다면, 1900년대생으로 간주하고 연도에 1900을 더합니다.
  3. 그렇지 않다면, 2000년대생으로 간주하고 연도에 2000을 더합니다.
  4. 추출한 연, 월, 일 정보를 DATE 함수에 전달하여 날짜 형식으로 변환합니다.

외국인 등록번호 포함한 생년월일 추출

외국인 등록번호도 주민등록번호와 유사한 체계를 가지고 있지만, 7번째 자리의 숫자가 다릅니다. 외국인을 포함한 모든 경우의 생년월일을 추출하기 위한 수식은 다음과 같습니다:

=IF(OR(MID(A1,8,1)="3",MID(A1,8,1)="4",MID(A1,8,1)>="7"),
DATE("20"&MID(A1,1,2),MID(A1,3,2),MID(A1,5,2)),
DATE("19"&MID(A1,1,2),MID(A1,3,2),MID(A1,5,2)))

이 수식은 주민번호의 8번째 자리가 3, 4이거나 7 이상인 경우(2000년대생)와 그렇지 않은 경우(1900년대생)를 구분하여 생년월일을 추출합니다.

텍스트 나누기 기능을 활용한 방법

엑셀의 '텍스트 나누기' 기능을 사용하여 주민번호에서 생년월일을 추출할 수도 있습니다. 이 방법은 함수를 사용하지 않고도 간단하게 생년월일을 추출할 수 있다는 장점이 있습니다.

  1. 주민번호가 있는 열을 선택합니다.
  2. '데이터' 탭에서 '텍스트 나누기'를 클릭합니다.
  3. '구분 기호로 분리' 옵션을 선택하고 '다음'을 클릭합니다.
  4. 구분 기호로 '기타'를 선택하고 '-'를 입력한 후 '다음'을 클릭합니다.
  5. 각 열의 데이터 형식을 설정하고 '마침'을 클릭합니다.

이 방법은 간단하지만, 1929년 이전 출생자의 경우 날짜가 제대로 변환되지 않을 수 있다는 한계가 있습니다. 엑셀에서는 2자리 연도를 입력할 때 '2029 룰'에 따라 00부터 29까지는 2000년대로, 30부터 99까지는 1900년대로 변환하기 때문입니다.

DATEVALUE와 TEXT 함수를 활용한 방법

DATEVALUE 함수와 TEXT 함수를 조합하여 생년월일을 추출하는 방법도 있습니다:

=DATEVALUE(TEXT(LEFT(A1,6),"00-00-00"))

하지만 이 방법도 '텍스트 나누기'와 마찬가지로 2029 룰에 의해 날짜가 잘못 변환될 수 있습니다.

중간 작업 열을 활용한 방법

실무에서는 중간 작업 열을 추가하여 단계별로 정보를 추출하는 방법도 유용합니다:

  1. 연도 추출: =MID(A1,1,2)
  2. 월 추출: =MID(A1,3,2)
  3. 일 추출: =MID(A1,5,2)
  4. 7번째 자리 추출: =--MID(A1,8,1) (마이너스를 두 번 입력하여 텍스트를 숫자로 변환)
  5. 생년월일 변환: =IF(OR(D1=3,D1=4,D1>=7),DATE("20"&B1,C1,E1),DATE("19"&B1,C1,E1))

이 방법은 한 번에 생년월일을 추출할 수는 없지만, 각 단계별로 정보를 확인하고 수정할 수 있어 복잡한 데이터 처리에 유용합니다.

결론

엑셀에서 주민번호를 이용해 생년월일을 추출하는 방법은 다양합니다. 가장 정확한 방법은 IF 함수와 DATE 함수를 조합하여 주민번호의 7번째 자리를 확인하고, 이를 바탕으로 1900년대생과 2000년대생을 구분하는 것입니다. 이 방법은 외국인 등록번호를 포함한 모든 경우에 적용할 수 있으며, 정확한 생년월일을 추출할 수 있습니다.

실무에서는 데이터의 특성과 필요에 따라 적절한 방법을 선택하여 사용하면 됩니다. 간단한 데이터 처리라면 '텍스트 나누기' 기능을 활용하고, 정확한 생년월일이 필요하다면 함수를 조합한 방법을 사용하는 것이 좋습니다.

자주 묻는 질문

주민번호에서 생년월일만 추출하는 가장 간단한 방법은 무엇인가요?

가장 간단한 방법은 LEFT와 MID 함수를 사용하는 것입니다. 예를 들어, =LEFT(A1,2)&"년 "&MID(A1,3,2)&"월 "&MID(A1,5,2)&"일"과 같은 수식을 사용할 수 있습니다. 하지만 이 방법은 완전한 연도를 표시하지 못합니다.

2000년대생의 생년월일을 정확하게 추출하려면 어떻게 해야 하나요?

주민번호의 7번째 자리(하이픈 뒤 첫 번째 숫자)를 확인하여 1900년대생과 2000년대생을 구분해야 합니다. IF 함수와 DATE 함수를 조합한 수식을 사용하면 됩니다.

외국인 등록번호에서도 생년월일을 추출할 수 있나요?

네, 외국인 등록번호도 주민등록번호와 유사한 체계를 가지고 있어 같은 방법으로 생년월일을 추출할 수 있습니다. 다만, 7번째 자리의 숫자가 다르므로 이를 고려한 수식을 사용해야 합니다.

엑셀의 '2029 룰'이란 무엇인가요?

엑셀에서 2자리 연도를 입력할 때, 00부터 29까지는 2000년대로, 30부터 99까지는 1900년대로 자동 변환하는 규칙입니다. 이로 인해 1929년 이전 출생자의 생년월일이 잘못 변환될 수 있습니다.

주민번호에서 생년월일 외에 다른 정보도 추출할 수 있나요?

네, 주민번호의 7번째 자리를 통해 성별과 출생 연대를 알 수 있으며, 8-12번째 자리는 출생지역 등의 정보를 포함하고 있습니다. 이러한 정보도 엑셀 함수를 활용하여 추출할 수 있습니다.