Excel에서 주소 목록에서 우편번호를 추출하는 방법은 무엇입니까?

Excel에서 고객 정보를 관리할 때 일반적으로 각 항목에 거리 정보와 우편번호(우편번호)가 포함된 전체 주소 목록을 보유하게 됩니다. 만약 주소에서 우편번호만 분리하고 추출해야 하는 경우 - 예를 들어 메일링 리스트, 지역 분석 또는 데이터 형식 지정을 위해 - 이를 하나씩 수동으로 분리하는 것은 대규모 데이터 세트에서는 지루하고 오류가 발생하기 쉽습니다. 다행히도 Excel에는 수동 작업 없이 여러 개의 우편번호를 빠르고 효율적으로 추출할 수 있는 실용적인 방법들이 있습니다. 이 문서에서는 Excel 공식과 VBA 매크로 솔루션을 사용하여 주소에서 우편번호를 추출하는 단계별 지침을 제공합니다.

Excel에서 공식을 사용하여 우편번호 추출

Excel에서 사용자 정의 함수를 사용하여 우편번호 추출

Excel에서 공식을 사용하여 우편번호 추출

대부분의 일반적인 주소 목록에서 우편번호는 주소 문자열의 마지막에 위치하며 길이는 8자리 이상이 아닙니다. 이 방법은 주소 목록의 모든 주소가 일관된 형식을 따르며 우편번호가 공백으로 구분되어 각 주소의 마지막 요소로 나타나는 경우에 가장 적합합니다.

공식을 사용하여 우편번호를 신속하게 추출하려면 다음 단계를 따르세요:

1. 우편번호를 표시하려는 빈 셀을 선택하세요 (예: 주소가 A1에 있다면 B1). 다음 공식을 입력하세요:

=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))

2. Enter 키를 누릅니다. A1의 주소에서 우편번호가 선택한 셀에 표시됩니다.

3. 이 공식을 다른 주소에 적용하려면, 공식이 있는 셀을 선택하고 채우기 핸들을 열 아래로 드래그하여 모든 주소 행을 덮으세요. Excel이 각 주소의 우편번호를 자동으로 추출합니다.

팁: 이 공식은 우편번호가 주소 문자열에서 마지막 공백 이후의 마지막 항목이라고 가정합니다. 주소의 구조가 다르다면, 예를 들어 우편번호가 중간에 있거나 쉼표로 구분되는 경우, 이 공식은 정확한 결과를 반환하지 않을 수 있습니다. 국제 우편번호 또는 사용자 정의 형식에 대해 이 방법을 확장하려면 더 다를 수도 있고 더 고급 공식 접근 방식이 필요할 수 있으며, 아래의 VBA 솔루션을 시도해 보세요.

Excel에서 사용자 정의 함수로 우편번호 추출

주소 데이터가 더 복잡하고 일관되지 않거나, 다양한 형식 및 문자열 내의 위치에 우편번호가 포함되어 있다면, VBA 매크로는 더 유연한 추출 옵션을 제공할 수 있습니다. 이 자동화된 방법은 주소 전체 열을 신속히 처리하여 우편번호만 분리하고 반환하여 상당한 시간을 절약하고 수작업을 최소화합니다.

1. Alt + F11을 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. VBA 창에서 삽입 > 모듈을 클릭하여 새 모듈을 생성합니다. 다음 VBA 코드를 모듈 창에 복사하여 붙여넣기 합니다.

Public Function ExtractPostcode(text As String) As String
    Dim reg As New RegExp
    Dim m As MatchCollection
    reg.Pattern = "\b([A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}|\d{5}(?:-\d{4})?|\d{6})\b"
    reg.IgnoreCase = True
    reg.Global = False
    
    If reg.Test(text) Then
        Set m = reg.Execute(text)
        ExtractPostcode = m(0).Value
    Else
        ExtractPostcode = ""
    End If
End Function

3. 코드를 붙여넣은 후, vba 에디터 창에서 도구 > 참조를 선택합니다. 스크린샷을 참고하세요:

4. 참조 대화상자에서 Microsoft VBScript Regular Expressions 5.5를 체크하고 확인을 클릭합니다.

5. 워크시트로 돌아가서, 이 공식을 입력합니다: =ExtractPostcode(A2), 채우기 핸들을 다른 셀로 드래그합니다. 모든 우편번호가 즉시 표시됩니다. 스크린샷을 참고하세요:

팁: 이 코드를 통해 몇 초 안에 Excel에서 어떤 국가나 지역의 우편번호도 자동으로 추출할 수 있습니다. 정규식을 대상 지역의 우편번호 규칙에 맞게 조정하면 UK의 “SW1A 1AA,” 미국의 “12345-6789,” 또는 중국의 “100000”과 같은 다양한 형식에 쉽게 적응할 수 있으며, 데이터 정리 및 분석 효율성을 크게 향상시킬 수 있습니다.

관련 기사:

• Excel에서 이름에서 이니셜 추출하기
• Excel에서 문자열에서 소수점 값 추출하기