Excel 엑셀에서 주민 번호에 하이픈 넣기 일반적인 주민등록번호 서식으로 변경하기

주민등록번호와 같은 중요한 개인정보는 Excel로 관리하면 효과적으로 기록하고 추적할 수 있습니다. 하지만 때로 특정 사용자 또는 여러 요인들로 인해 기준이 없이 관리하다 보면 위 그림과 같이 (-)하이픈이 없는 주민등록번호 형식의 자료를 접하는 경우가 있습니다. 이런 방식의 주민등록번호 데이터들은 가독성이 상당히 떨어지고, 차후 정보 입력 및 검색에 불편함을 초래할 수 있습니다.

하이픈을 추가하여 주민등록번호를 보기 쉽고 이해하기 쉬운 형식으로 변환 시 데이터의 가독성이 향상되고, 실수를 줄일 수 있는데요. 하이픈으로 나눈 주민등록번호는 Excel의 필터, 정렬 등의 기능을 보다 효과적으로 활용할 수 있습니다. 또한 하이픈이 있는 주민등록번호는 데이터 공유나 출력 시에도 일관된 형식을 유지하며, 다른 시스템과의 연동에 용이해 여러모로 권장되는 방식입니다.

만약 사용 중인 Excel 자료에서 하이픈이 없는 주민등록번호 데이터들이 수동으로 수정할 범위를 넘어선 경우, 셀 서식 또는 VBA를 활용하면 손쉽게 하이픈을 추가할 수 있으며 자세한 수정 과정은 다음과 같습니다.

튜토리얼 환경: 윈도우 11, 마이크로소프트 365 엑셀 2023

셀 서식으로 하이픈 추가하기

Excel의 셀 서식 대화상자는 셀에 적용되는 서식을 설정하는 데 사용되는 도구입니다. 이 대화상자를 통해 셀의 숫자, 날짜, 텍스트 등의 형식, 정렬, 글꼴, 배경색 등 다양한 서식 옵션을 조정할 수 있으며 특정 텍스트의 위치에 하이픈을 추가할 수 도 있습니다.

기타 형식으로 하이픈 추가

1. 홈 메뉴 중 표시 형식리본 메뉴의 확장 아이콘을 클릭합니다.

2. 셀 서식 옵션 창이 열리게 되는데 여기서 표시 형식탭의 범주(C): 섹션에서 기타 를 선택합니다. 이어서 우측 형식(T): 목록에서 주민등록번호 를 찾아 선택 후 확인을 클릭합니다.

3. 대상 통합문서를 확인하면 선택한 주민등록번호 데이터에 하이픈이 모두 추가된 것을 확인할 수 있습니다.

사용자 지정 형식으로 하이픈 추가

기타 범주가 아닌 사용자 지정 범주를 통해서도 주민등록번호사이에 하이픈 기호를 추가할 수 있습니다.

1. 이번에는 표시 형식탭의 범주(C): 섹션에서 사용자 지정 을 선택합니다. 이어서 우측 형식(T): 필드에 에서 주민등록번호 형식을 만들기 위해 ######-####### 와 같이 작성 후 확인을 클릭합니다.

2. 대상 통합문서를 확인하면 선택한 주민등록번호 데이터에 하이픈이 모두 추가된 것을 확인할 수 있습니다.

참고: 이 방식은 셀 내 데이터 자체는 변화가 없이 보여지는 서식만 변경되는 것 입니다.

VBA로 하이픈 추가하기

사용자 서식으로 하이픈을 추가하는 것이 일반적이고 초보 사용자들도 간단하게 추가할 수 있지만 셀 내 데이터 자체는 변화가 없이 보여지는 서식만 변경되는 것으로 데이터 자체도 변경하고 싶다거나 하이픈이 없는 데이터를 자주 변환해야 한다면 Excel의 내장 프로그램 언어인 VBA^{Visual Basic for Applications}를 사용해 자동화할 수 있습니다.

참고: 다음에 안내하는 방식은 주민등록번호가 13자리인 경우를 기준으로 작성된 것으로 13자리 주민등록번호에서만 하이픈이 추가되며, 그 외 상황에는 변경되지 않습니다.

1. Excel에서 주민등록번호에 하이픈을 추가할 통합 문서를 열고 상단 메뉴에서 개발 도구 탭 으로 이동 후 좌측 Visual Basic 리본 메뉴를 클릭합니다. 개발 도구 탭이 보이지 않는 경우 개발 도구를 활성화하는 방법을 확인하세요.

2. 해당 문서를 마우스 오른쪽 버튼으로 클릭해 컨텍스트 메뉴를 열고 삽입(N) > 모듈(M)을 선택합니다.

3. 통합문서 - Module1(코드) 창이 열리면 이곳에 스크립트를 작성하거나 붙여넣기 합니다. 다음 명령 사용 시 A2:A 열과 같이 특정 라인에 위치한 주민등록번호에 하이픈을 일괄적으로 삽입할 수 있습니다.

Sub ApplyHyphenToColumnA()
    Dim lastRow As Long
    Dim cell As Range

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For Each cell In Range("A2:A" & lastRow)
        If Len(cell.Value) = 13 Then
            cell.Value = Left(cell.Value, 6) & "-" & Right(cell.Value, 7)
        End If
    Next cell
End Sub

위 스크립트를 좀 더 알아보면

• Sub ApplyHyphenToColumnA(): 이 부분은 VBA 서브 프로시저^{Sub Procedure}를 정의하는 부분입니다. 이 프로시저는 ApplyHyphenToColumnA라는 이름으로 정의되었습니다. 프로시저^Procedure는 일련의 작업을 수행하기 위한 코드 블록입니다. 프로시저는 특정한 동작을 수행하고 결과를 반환하지 않습니다. 입력값을 받아들여 계산을 수행하고, 그 결과를 반환하는 함수^Function와 유사하면서도 목적과 사용법에서 차이가 있으니 다르게 이해해야 합니다.
• Dim lastRow As Long: lastRow라는 이름의 변수를 선언합니다. 이 변수는 열 A의 마지막 행 번호를 저장하기 위해 사용됩니다.
• Dim cell As Range: cell이라는 이름의 변수를 선언합니다. 이 변수는 반복문에서 현재 처리 중인 셀을 나타내기 위해 사용됩니다.
• lastRow = Cells(Rows.Count, 1).End(xlUp).Row: lastRow 변수에 열 A의 마지막 행 번호를 저장합니다. Cells(Rows.Count, 1)은 열 A의 마지막 셀을 의미하며, .End(xlUp)은 아래로 올라가며 비어있지 않은 마지막 셀을 찾는 메서드입니다.
• For Each cell In Range("A2:A" & lastRow): cell 변수를 사용하여 열 A의 각 셀을 순회하는 반복문을 시작합니다. 범위는 A2부터 마지막 행까지입니다. 이 부분을 수정하면 다른 열도 동일하게 동작하도록 정의할 수 있습니다.
• If Len(cell.Value) = 13 Then: 현재 처리 중인 셀의 값의 길이가 13인지 확인합니다. 주민등록번호는 13자리여야 하므로, 이 조건을 만족해야 하이픈을 적용할 수 있습니다.
• cell.Value = Left(cell.Value, 6) & "-" & Right(cell.Value, 7): 주민등록번호의 앞 6 자리와 뒤 7 자리를 가져와서 사이에 하이픈을 추가합니다. 이렇게 하면 예시로 제시한 7701021234567과 같은 형식의 주민등록번호를 770102-1234567 형태로 변경합니다.
• Next cell: 반복문에서 다음 셀로 이동합니다. VBA 더 알아보기

4. 기본적으로 일반 통합문서(.xlsx)는 매크로를 지원하지 않기 때문에 VBA 또는 매크로를 사용할 수 없습니다. 매크로를 포함한 경우.xlsm형식으로 저장해야 합니다. 저장 메뉴를 선택 후 파일 형식(T):을 Excel 매크로 사용 통합 문서로 변경 후 저장(S)을 클릭합니다.

5. 실행하면 다음과 같이 지정한 열에 데이터에 모두 하이픈이 추가되는 것을 볼 수 있습니다. 스크립트를 위와 같이 바로 실행하거나 매크로 또는 버튼에 할당해 사용하면 언제든 편하게 하이픈을 삽입할 수 있습니다.

만약, 특정 라인이 아닌 선택한 셀 영역에서만 주민등록번호에 하이픈을 삽입하고 싶다면 다음 스크립트를 사용합니다.

Sub ApplyHyphenToSelectedCell()
    Dim selectedCell As Range

    Set selectedCell = Selection

    If Len(selectedCell.Value) = 13 Then
        selectedCell.Value = Left(selectedCell.Value, 6) & "-" & Right(selectedCell.Value, 7)
    End If
End Sub

하이픈 삭제하기

하이픈이 불필요하다면 불필요한 셀만 선택해서 텍스트를 삭제하는 것만으로 충분하지만 특정 영역 또는 통합문서 전체의 하이픈을 모두 삭제하고 싶은 상황에서는 Excel의 찾기 및 바꾸기 메뉴를 활용합니다.

참고: 서식으로 변경된 경우는 서식 자체를 수정하면 하이픈이 표시되지 않도록 수정할 수 있고, 실제 데이터에 하이픈이 추가되었다면  다음 과정으로 하이픈을 일괄적으로 삭제할 수 있습니다.

1. 위와 같이 셀 내 데이터에 하이픈이 적용된 경우라면 상단 탭을 홈으로 전환합니다.

2. 오른쪽 찾기 및 선택 리본 메뉴를 클릭해 하위 메뉴를 확장 하고 바꾸기(R) 를 선택합니다. 또는 Windows는 Ctrl + H macOS에서는 command + H 바로가기 키를 입력합니다.

3. 찾기 및 바꾸기 대화상자가 생성되는데 여기서 상단 탭을 바꾸기(P)로 두고 찾을 내용(N):필드에 - , 바꿀 내용(E): 필드는 공란으로 두고 모두 바꾸기(A) 버튼을 클릭합니다.

4. 하이픈이 모두 제거된 것을 볼 수 있습니다.

마치며

마이크로소프트 오피스 엑셀에서 일반적인 주민등록번호 서식으로 사용하기 위해 주민등록번호에 하이픈을 넣는 방법에 대해 알아보았습니다. 주민등록번호를 하이픈으로 나누어 엑셀로 관리하는 것은 데이터의 가독성과 안정성을 높이는 중요한 과정입니다.

주민등록번호를 올바른 형식으로 관리함으로써 개인정보 보호에 기여할 뿐만 아니라 데이터의 효율적인 관리와 활용에도 도움이 됩니다. 하이픈으로 주민등록번호를 나누어 엑셀에서 관리함으로써 데이터의 정확성과 보안을 유지하고, 원활한 데이터 처리를 가능하게 하니 위 과정으로 하이픈을 일괄적으로 추가해서 관리하고 사용해 보시길 바랍니다.

관련 글

• Excel 엑셀 오늘 날짜 및 현재 시간 자동 갱신 방법 과 단축키 정리
• 엑셀 들여쓰기 내어쓰기 텍스트 맞춤 분할 정렬 메뉴 사용 방법
• 엑셀 Excel 제한된 보기 경고 정리 해제 방법 비활성화 방법
• 엑셀에서 표를 만들고 사용 할 수 있는 기능 정리 슬라이서 요약행