Excel 엑셀 주민등록번호 뒷자리 별표 변환 마스킹하기 안보이게 가리기

Microsoft Office의 Excel은 개인정보와 같은 중요한 데이터 또는 대량의 자료를 처리하고 관리하는 데에 유용한 스프레드시트 도구입니다. 이렇게 여러 종류의 데이터를 다루다보면 고객 정보와 같은 중요하게 관리되어야 하는 자료들이 존재하는데 보통 이런 개인 정보들은 MySQL과 같은 데이터베이스(DB) 시스템을 통해 이루어지는 것이 일반적입니다. 이런 DB에서는 ******* 와 같이 별표로 주민번호 뒷자리를 마스킹하는 것과 같은 작업을 SET 주민번호 = CONCAT(LEFT(주민번호, LENGTH(주민번호)-8), '-*******'); 형태의 쿼리문으로 간단하게 수정할 수 있는데요.

하지만 DB에서 CSV 형식으로 고객 정보를 내려받아 가공을 해야하거나 Excel 자체적으로 직접 고객 정보를 관리하는 경우가 있습니다. 이런 상황에는 쿼리 사용에 한계가 있어 수작업으로 주민등록 번호를 마스킹하는 것도 심심찮게 볼 수 있습니다. 이 매뉴얼은 이러한 상황을 위해 Excel을 사용하여 주민번호 뒷자리를 마스킹하는 기본적인 방법을 안내합니다.

튜토리얼 환경: 마이크로소프트 365 엑셀 2023

서식으로 마스킹하기

Excel에서 대상 셀의 서식이 숫자인 경우에는 특정 텍스트 위치를 자르고 변환하는 작업을 수행할 수 없으니, 일반적으로 서식 대화상자를 통해 보이는 서식만 별표로 마스킹 되도록 지정합니다.

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

2. 셀 서식 옵션 창이 열리게 되는데 여기서 표시 형식탭의 범주(C): 섹션에서 사용자 지정 을 선택합니다. 이어서 우측 형식(T): 필드에 000000-0,,"******" 이라고 입력 후 확인을 클릭합니다.

3. 서식이 적용되어 주민등록번호 마지막 6자리 숫자가 별표로 마스킹처리 되었습니다. 다만, 이 방식은 서식만 변경되는 것으로 실제 데이터는 그대로 유지됩니다.

형식(T)필드에 000000-0,, 이렇게 만 입력하면 뒤의 6자리 부분이 완전히 보이지 않게 되니 필요한 경우 이 방식도 참고합니다.

Replace 함수로 마스킹하기

대상 셀의 서식이 숫자가 아닌 텍스트로 되어 있다면 Replace함수를 사용해 주민등록번호의 뒷자리를 별표로 마스킹 하거나 원하는 형태로 가공할 수 있습니다.

1. 셀 서식이 숫자가 아닌 경우 하이픈을 추가한 상태로 데이터를 처리하는 것이 효율적이니 하이픈이 추가된 상태를 기준으로 설명합니다. 현재 데이터에 하이픈이 적용되지 않았다면 하이픈을 추가합니다. 하이픈을 추가하는 방법은 주민등록번호에 하이픈 삽입하기를 확인하세요.

2. 오른쪽에 새로운 마스킹 할 값이 들어갈 열을 추가 후 해당 열에 =REPLACE(A2, 9, 6, "******")라고 작성 후 자동 채우기 합니다. 자동 채우기 자세히 알아보기
   • A2: 텍스트를 변경하고자 하는 대상 셀을 나타냅니다. 이 경우에는 주민등록번호가 있는 셀을 지정합니다.
   • 9: 대상 텍스트에서 변경할 부분의 시작 위치를 나타냅니다. 이 경우에는 주민등록번호에서 뒷자리의 시작 위치를 나타냅니다.
   • 6: 대상 텍스트에서 변경할 부분의 길이를 나타냅니다. 이 경우에는 주민등록번호에서 뒷자리의 길이를 나타냅니다.
   • "******": 변경할 대상 텍스트를 나타냅니다. 이 경우에는 주민등록번호의 뒷자리를 "******"로 대체하여 가려주는 역할을 합니다.

참고: 만약 주민등록번호 뒤 6자리를 삭제하고 싶다면 "******"를 ""로 수정해 =REPLACE(A2, 9, 6, "")과 같이 사용합니다.

VBA로 마스킹하기

위 서식 또는 함수를 이용 방식만으로도 일반적인 큰 불편함은 없지만, 마스킹 하는 작업 빈도가 높은 경우 VBA스크립트를 매크로 또는 버튼 등에 할당해서 자동화하면 좀 더 효율적으로 처리할 수 있습니다.

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

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

3. 통합문서 - Module1(코드) 창이 열리면 이곳에 스크립트를 작성하거나 붙여넣기 합니다. 다음 명령 사용 시 해당 통합문서의 활성화된 Sheeet에 포함된 주민등록번호 형식(예: 123456-1234567)의 모든 셀에서 뒤 6자리 숫자가 별표로 마스킹 처리 됩니다.

Sub MaskNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim regex As Object
    Dim pattern As String

    pattern = "(\d{6}-\d{1})\d{6}"
    Set regex = CreateObject("VBScript.RegExp")
    Set rng = ActiveSheet.UsedRange

    For Each cell In rng
        If TypeName(cell.Value) = "String" Then
            With regex
                .Global = True
                .pattern = pattern
            End With

            cell.Value = regex.Replace(cell.Value, "$1******")
        End If
    Next cell

    Set regex = Nothing

    MsgBox "주민등록번호 뒷자리 마스킹이 완료되었습니다.", vbInformation
End Sub

위 명령을 좀 더 자세하게 정리하면

• Sub MaskNumbers(): VBA 서브 프로시저(Sub Procedure)를 정의하는 부분입니다. 이 프로시저는 "MaskNumbers"라는 이름으로 정의되었습니다. 프로시저는 일련의 작업을 수행하기 위한 코드 블록입니다.
• Dim rng As Range: rng이라는 이름의 Range 변수를 선언합니다. 이 변수는 처리할 범위를 저장하기 위해 사용됩니다.
• Dim cell As Range: cell이라는 이름의 Range 변수를 선언합니다. 이 변수는 반복문에서 현재 처리 중인 셀을 나타내기 위해 사용됩니다.
• Dim regex As Object: regex라는 이름의 Object 변수를 선언합니다. 이 변수는 정규식 처리를 위한 VBScript.RegExp 객체를 저장하기 위해 사용됩니다.
• Dim pattern As String: pattern이라는 이름의 String 변수를 선언합니다. 이 변수는 숫자 패턴을 정의하는 문자열을 저장하기 위해 사용됩니다.
• pattern = "(\d{6}-\d{1})\d{6}": pattern 변수에 (\d{6}-\d{1})\d{6}라는 숫자 패턴을 할당합니다. 이 패턴은 숫자 6자리와 하이픈(-) 숫자 1자리 그리고 마지막 숫자 6자리를 정규식 그룹으로 나눈 것입니다.
• Set regex = CreateObject("VBScript.RegExp"): regex 변수에 VBScript.RegExp 객체를 생성하여 할당합니다. 이 객체는 정규식 처리를 위해 사용됩니다.
• Set rng = ActiveSheet.UsedRange: rng 변수에 현재 시트의 사용된 범위를 할당합니다.
• For Each cell In rng: rng 범위 내의 각 셀을 순회하는 반복문을 시작합니다.
• With regex: regex 객체에 대한 설정을 수행하기 위해 With 구문을 사용합니다.
• .Global = True: 정규식 패턴을 전체 문자열에 대해 적용하도록 설정합니다.
• .Pattern = pattern: 정규식 패턴을 pattern 변수에 저장된 패턴으로 설정합니다.
• cell.Value = regex.Replace(cell.Value, "$1******"): 현재 처리 중인 셀의 값을 정규식 패턴에 맞게 마스킹 하여 업데이트합니다. 숫자 패턴을 찾으면 앞 부분은 그대로 두고 뒷 부분을 ""로 대체합니다.
• Next cell: 반복문에서 다음 셀로 이동합니다.
• Set regex = Nothing: regex 객체를 해제합니다.

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

5. 실행시 다음과 같이 주민등록번호 형식의 셀 값에서 마지막 6자리 숫자가 별표로 가려진 것을 볼 수 있습니다. 스크립트를 바로 실행하거나 매크로 또는 버튼에 할당하면 간편하게 적용할 수 있습니다.

기존 소스 데이터에 하이픈이 있거나 없거나 상관없이 모두 마스킹을 하고 싶은 경우 다음과 같이 수정된 스크립트를 사용합니다.

Sub MaskNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim regex As Object
    Dim pattern As String

    pattern = "(\d{6})-?(\d{1})(\d{6})"
    Set regex = CreateObject("VBScript.RegExp")
    Set rng = ActiveSheet.UsedRange

    For Each cell In rng
        If IsNumeric(cell.Value) Then
            cell.Value = Format(cell.Value, "0000000000000")

            With regex
                .Global = True
                .pattern = pattern
            End With

            If regex.Test(cell.Value) Then
                cell.Value = regex.Replace(cell.Value, "$1-$2******")
            End If
        ElseIf TypeName(cell.Value) = "String" Then
            With regex
                .Global = True
                .pattern = pattern
            End With

            If regex.Test(cell.Value) Then
                cell.Value = regex.Replace(cell.Value, "$1-$2******")
            End If
        End If
    Next cell

    Set regex = Nothing

    MsgBox "주민등록번호 뒷자리 마스킹이 완료되었습니다.", vbInformation
End Sub

만약 별 기호로 마스킹하는 것이 아닌 뒷부분을 완전하게 제거해야 하는 경우 다음 스크립트를 사용합니다. cell.Value = regex.Replace(cell.Value, "$1")이 부분을 수정하면 원하는 형태로 가공할 수 있으니 적절하게 활용합니다.

Sub deleteNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim regex As Object
    Dim pattern As String

    pattern = "(\d{6}-\d{1})\d{6}"
    Set regex = CreateObject("VBScript.RegExp")
    Set rng = ActiveSheet.UsedRange

    For Each cell In rng
        If TypeName(cell.Value) = "String" Then
            With regex
                .Global = True
                .pattern = pattern
            End With

            cell.Value = regex.Replace(cell.Value, "$1")
        End If
    Next cell

    Set regex = Nothing

    MsgBox "주민등록번호 뒷자리가 모두 제거 되었습니다.", vbInformation
End Sub

마치며

마이크로소프트 오피스 엑셀에서 주민 등록 번호의 뒷자리를 **** 별표로 마스킹하는 기본적인 방법에 대해 알아보았습니다. 이 기능을 통해 개인정보를 보호하고 민감한 정보가 노출되는 것을 방지할 수 있습니다.

주민등록번호와 같은 개인정보를 처리할 때는 항상 보안을 우선으로 고려해야 하는데요. Excel을 올바르게 활용하여 개인정보 보호에 기여하는 것은 중요한 일이니, 주민번호 뒷자리를 마스킹 해야 하는 경우 위 과정으로 간단하게 변환 후 사용해 보시길 바랍니다.

관련 글

• 크롬 브라우저 자동 완성 기록 삭제 및 자동 완성 기능 끄기
• Excel 엑셀 틀 고정으로 특정 행 또는 열 고정해서 사용하기
• Excel 엑셀 인쇄 페이지 점선 제거 지우기 없애기 하는 방법 모두 정리
• 구글 지메일 '보안 수준이 낮은 앱의 액세스' 사용 설정